Power BI Data Analyst Free Sample Questions

Description

Covers connecting to, profiling and transforming data, designing data models and DAX calculations, creating and enhancing reports for storytelling, and managing and securing Power BI workspaces.

20 free sample questions214 in the full practice test Other version: DA-100(47)

Try simulator

PL-300 Sample Questions

  1. Question 1

    Intermediate

    Prepare the data · Choose between DirectQuery and Import

    A financial services firm is developing a Power BI report to analyze stock market data. The primary data source is a large Azure SQL Database containing billions of transaction records. The report must provide sub-second query performance for visuals and allow analysts to explore the data using slicers. The data in the database is updated every few minutes. The firm wants to avoid data duplication and minimize data latency. Which storage mode configuration should be used for the main transaction table in the Power BI model?

    Answer and explanation

    Correct answer: B

    DirectQuery is the optimal choice for this scenario. It connects directly to the Azure SQL Database, avoiding data duplication and ensuring that the report reflects the most current data. Given the large volume of data (billions of records), Import mode would be impractical and lead to slow refreshes. DirectQuery sends queries to the source database, leveraging its processing power to handle large datasets and provide near real-time data with low latency.

  2. Question 2

    Beginner

    Prepare the data · Merge and append queries

    You are developing a Power BI report for a logistics company. You have two tables: 'Shipments' and 'Carriers'. The 'Shipments' table contains a 'CarrierID' column. The 'Carriers' table contains 'CarrierID' and 'CarrierName'. You need to add the 'CarrierName' to the 'Shipments' table to facilitate analysis. The 'Shipments' table is very large, with over 50 million rows. Which Power Query operation is the most performance-efficient for this task?

    Answer and explanation

    Correct answer: B

    Merge Queries is the correct operation. It is equivalent to a join in SQL and allows you to combine two tables based on a matching column, in this case 'CarrierID'. This operation adds columns from one table to another. Appending queries combines tables vertically by adding rows, which is not the desired outcome here. For large tables, merging is generally more efficient than trying to perform lookups row by row.

  3. Question 3

    Beginner

    Prepare the data · Create and transform columns

    You are cleaning a dataset in Power Query that contains a 'ProductSKU' column. The SKU is formatted as 'CAT-ID-SIZE', for example, 'SHRT-105-XL'. You need to extract the three parts ('CAT', 'ID', and 'SIZE') into separate columns. Which transformation provides the most direct way to achieve this?

    Answer and explanation

    Correct answer: C

    The 'Split Column by Delimiter' transformation is specifically designed for this purpose. By specifying the hyphen '-' as the delimiter, Power Query will automatically create new columns containing the separated parts of the string. This is the most efficient and direct method for this common data cleaning task.

  4. Question 4

    Advanced

    Model the data · Implement role-playing dimensions

    A hospital analyst is creating a Power BI model to track patient readmissions. The model includes a 'Patients' dimension table and an 'Admissions' fact table. The hospital wants to analyze admissions based on both the admission date and the discharge date. Both dates need to relate to a central 'Calendar' dimension table. How should this be implemented in the data model to avoid ambiguity and follow best practices?

    Answer and explanation

    Correct answer: B

    This is the classic implementation of a role-playing dimension. The 'Calendar' table plays multiple roles (admission calendar and discharge calendar). Power BI only allows one active relationship between two tables. The best practice is to set the primary relationship (e.g., on Admission Date) as active and create an inactive relationship for the secondary date. DAX measures can then activate the inactive relationship on-demand using the USERELATIONSHIP function, allowing for flexible analysis without creating model ambiguity.

  5. Question 5

    Intermediate

    Model the data · Optimize model performance

    You are optimizing a Power BI data model for a retail company. The model contains a large fact table, 'Sales', with 100 million rows. You notice that several visuals are slow to render. Using Performance Analyzer, you identify that a measure calculating the 'Year-to-Date Sales' is the primary bottleneck. The current DAX formula for the measure is: YTD Sales = TOTALYTD(SUM(Sales[SalesAmount]), 'Calendar'[Date]). What is the most likely cause of the poor performance?

    Answer and explanation

    Correct answer: B

    Time intelligence functions in DAX, such as TOTALYTD, rely on a properly configured date table. If the 'Calendar' table is not officially marked as a date table in the model properties, the DAX engine cannot use its optimized time intelligence algorithms. Instead, it falls back to a much slower, less efficient calculation method, which becomes a significant performance bottleneck on large fact tables. Marking the table as a date table is a critical optimization step.

  6. Question 6

    Intermediate

    Model the data · Create model calculations by using DAX

    You need to create a measure that calculates the total sales for only the 'Bikes' product category, regardless of any other filters applied to the report, such as year or region. Which DAX formula correctly accomplishes this?

    Answer and explanation

    Correct answer: C

    This formula uses the CALCULATE function to modify the filter context. The FILTER function iterates over a table specified by the ALL(Products) function. ALL(Products) removes any existing filters from the 'Products' table, ensuring the calculation considers all products initially. FILTER then applies a new condition to include only rows where the category is 'Bikes'. This correctly isolates the 'Bikes' category sales, ignoring other report filters on the 'Products' table.

  7. Question 7

    Intermediate

    Model the data · Create model calculations by using DAX

    A marketing team wants to analyze campaign performance. They have a report with a slicer for 'Campaign Name'. They want to see a card visual that always shows the total marketing budget for all campaigns, which should not change when they select specific campaigns in the slicer. Which DAX function is essential to create the measure for this card visual?

    Answer and explanation

    Correct answer: B

    The ALL function is used to remove filters from a table or columns. To create a measure that is unaffected by the 'Campaign Name' slicer, you would wrap the 'Campaigns' table or the 'Campaign Name' column within an ALL function inside a CALCULATE statement. For example: Total Budget = CALCULATE(SUM(Campaigns[Budget]), ALL(Campaigns)). This removes the filter context applied by the slicer, ensuring the sum is always calculated over all campaigns.

  8. Question 8

    Intermediate

    Visualize and analyze the data · Create custom tooltips

    You are designing a report for a sales manager. The manager wants to see a matrix of 'Sales Amount' by 'Region' and 'Product Category'. When the manager selects a specific region from a slicer, they want a tooltip to appear over the total sales amount for that region, showing a breakdown of sales by individual salesperson within that region. What should you configure to enable this functionality?

    Answer and explanation

    Correct answer: B

    Creating a report page tooltip is the correct solution. You would create a new, smaller report page, enable it as a tooltip in the page settings, and add a visual (like a bar chart) showing sales by salesperson. Then, on the main matrix visual, you would set its tooltip property to use this new report page. This allows for rich, data-driven tooltips that provide additional context on hover.

  9. Question 9

    Intermediate

    Manage and secure Power BI · Implement row-level security roles

    A university needs to secure its Power BI reports. Each department (e.g., Engineering, Arts, Medicine) should only see data related to its own students and faculty. The data model has a single 'Departments' table and a single 'FactEnrollment' table. A BI developer needs to implement security so that a user who is a member of the 'Engineering' department can only view 'Engineering' data. What is the most scalable and manageable way to implement this security requirement in the Power BI service?

    Answer and explanation

    Correct answer: B

    Dynamic Row-Level Security (RLS) is the most scalable solution. It involves creating a single security role with a DAX filter expression that dynamically filters data based on the user's identity, typically their User Principal Name (UPN) or email. This requires a lookup table that maps users to their respective departments. This approach allows for a single report to serve all departments while ensuring data is securely filtered, and it is much easier to manage than creating separate reports or static roles for each department.

  10. Question 10

    Advanced

    Manage and secure Power BI · Identify when a gateway is required

    Your company has a critical sales report built on an on-premises SQL Server Analysis Services (SSAS) tabular model. The report must always display the most up-to-date information without any scheduled delay. You need to publish this report to the Power BI service. Which connection type and gateway configuration must you use?

    Answer and explanation

    Correct answer: C

    A Live Connection is required when connecting to SSAS, as it creates a direct link to the model without importing data or the model structure into Power BI. Because the SSAS instance is on-premises, an on-premises data gateway (in standard mode) is mandatory to act as a bridge, securely relaying queries from the Power BI service to the on-premises SSAS server. This combination ensures real-time data access.

Register free for 10 more questions

Or unlock all 214 PL-300 questions with explanations, timed mode and flashcards.