Power BI Data Analyst · 27% of the exam

Model the data: free practice questions

5 sample questions from our 68-question bank for this domain — answers and explanations included. These are the same scenario-based style as the real Microsoft exam.

1. You are building a Power BI model for a financial services firm. You need to create a measure that calculates the running total of revenue from the start of the fiscal year to the current date. The fiscal year starts on April 1. Your Date table has a Date column and is marked as a date table. Which DAX approach is MOST appropriate?

  • A. Use TOTALYTD with the year-end date argument set to '3/31': `Revenue YTD = TOTALYTD([Total Revenue], Date[Date], "3/31")`✓ Correct
  • B. Use DATESYTD with a default calendar year: `Revenue YTD = CALCULATE([Total Revenue], DATESYTD(Date[Date]))`
  • C. Use DATESBETWEEN from January 1 to the last date in context: `Revenue YTD = CALCULATE([Total Revenue], DATESBETWEEN(Date[Date], DATE(YEAR(TODAY()),1,1), LASTDATE(Date[Date])))`
  • D. Use SAMEPERIODLASTYEAR to shift the date range back by one year: `Revenue YTD = CALCULATE([Total Revenue], SAMEPERIODLASTYEAR(Date[Date]))`
Explanation

TOTALYTD accepts an optional year-end date argument (as a string in MM/DD format). For a fiscal year ending March 31, specifying '3/31' correctly calculates the year-to-date total from April 1 through the current date in context. Option B is wrong — DATESYTD without the year-end argument defaults to December 31, which would produce a calendar YTD, not a fiscal YTD starting April 1. Option C is wrong — hardcoding January 1 calculates a calendar YTD and does not respect the fiscal year boundary. Option D is wrong — SAMEPERIODLASTYEAR shifts dates back one year and is used for year-over-year comparisons, not YTD calculations.

2. A Power BI developer writes the following measure: `High Value Orders = CALCULATE(COUNTROWS(Orders), Orders[OrderTotal] > 1000)` A report visual has a slicer filtering on Year = 2024. When the visual renders, what filter context does the CALCULATE function evaluate?

  • A. Only the explicit filter Orders[OrderTotal] > 1000, because CALCULATE replaces all external filters.
  • B. The slicer filter for Year = 2024 combined with the explicit filter Orders[OrderTotal] > 1000, because CALCULATE merges new filters with the existing filter context.✓ Correct
  • C. Only the slicer filter for Year = 2024, because CALCULATE ignores explicit filter arguments when an external filter already exists.
  • D. No filters at all, because CALCULATE always evaluates in an unfiltered context first before applying its arguments.
Explanation

CALCULATE evaluates its expression in a modified filter context. It starts with the current filter context (the slicer filter Year = 2024) and then applies or overrides filters from its filter arguments. Since the explicit filter is on a different column (OrderTotal), both filters are active simultaneously — the result counts rows where Year = 2024 AND OrderTotal > 1000. Option A is wrong — CALCULATE does not replace all external filters; it only replaces filters on the same columns as its explicit arguments. Option C is wrong — CALCULATE does not ignore its own filter arguments. Option D is wrong — CALCULATE does not clear the filter context; ALL() or REMOVEFILTERS() would be needed for that.

3. In a Power BI semantic model, a developer creates the following calculated column in the Sales table: `Sales Category = IF(Sales[Amount] > 1000, "High", "Low")` A colleague suggests converting this to a measure instead. Which TWO statements correctly describe a disadvantage of using a calculated column compared to a measure for this scenario? (Select TWO.)

  • A. Calculated columns are computed at data refresh time and stored in memory, increasing model size.✓ Correct
  • B. Calculated columns cannot use IF statements; only measures support conditional logic.
  • C. Calculated columns evaluate in row context, so they cannot directly respond to slicer selections at report runtime.✓ Correct
  • D. Calculated columns cannot reference columns from the same table.
  • E. Calculated columns always produce less accurate results than measures for aggregation scenarios.
Explanation

Options A and C are correct. A: Calculated columns are materialized at refresh time and stored in the VertiPaq engine, consuming memory proportional to the number of rows — a disadvantage compared to measures, which are computed on-demand. C: Calculated columns operate in row context during refresh and their values are fixed; they do not dynamically respond to user-applied filters or slicers at report runtime, unlike measures which recalculate within the current filter context. Option B is incorrect — calculated columns fully support IF and any scalar DAX functions. Option D is incorrect — calculated columns can reference any column in the same or related tables. Option E is incorrect — accuracy is not inherently different; the distinction is about when and how computations occur.

4. You are designing a star schema for a Power BI semantic model for an airline company. Your fact table, Flights, contains: FlightID, DepartureCityID, ArrivalCityID, FlightDateKey, PassengerCount, Revenue. You have a Cities dimension table with: CityID, CityName, Country, Region. You need to support filtering and grouping by both departure city and arrival city attributes simultaneously. What is the BEST approach to model this requirement?

  • A. Create two separate copies of the Cities dimension table (e.g., DepartureCities and ArrivalCities), each with an active relationship to the Flights fact table on the respective city ID column.✓ Correct
  • B. Set the cross-filter direction on the single Cities-to-Flights relationship to Both so that city filters apply to both DepartureCityID and ArrivalCityID.
  • C. Create a single active relationship from Cities[CityID] to Flights[DepartureCityID], and use USERELATIONSHIP in every measure that needs to filter by ArrivalCityID.
  • D. Merge the Flights table with the Cities table twice in Power Query to embed all departure and arrival city columns directly in the fact table, avoiding the need for any relationships.
Explanation

Correct: The standard and recommended approach for role-playing dimensions is to create separate copies of the dimension table — one for each role (DepartureCities and ArrivalCities). Each copy has its own active relationship to the fact table, allowing slicers and visuals to filter independently by departure and arrival city without requiring USERELATIONSHIP in every measure. | Wrong (B): A single Cities table cannot have two active relationships to the same fact table simultaneously. Setting cross-filter to Both does not solve the problem of needing two independent city filter contexts. | Wrong (C): While USERELATIONSHIP is a valid technique, it must be added to every measure that needs arrival city filtering, making the model harder to maintain. This is inferior to the role-playing dimension table approach for broad filtering needs. | Wrong (D): Embedding all attributes directly in the fact table is a denormalization anti-pattern. It increases table size, reduces compression efficiency in VertiPaq, and eliminates the benefits of a star schema (e.g., shared dimension slicers).

5. You are creating a Power BI report for a sales team. You have a Products table and a Sales table with a many-to-one relationship from Sales to Products. The cross-filter direction is set to Single (Products → Sales). A report user wants a visual that shows, for each product category, only the categories that have had at least one sale. When they place Category on the visual, all categories appear — including those with no sales. What change should you make?

  • A. Change the cross-filter direction on the relationship to Both (bidirectional).
  • B. Add a measure that counts sales and use it as a visual-level filter to exclude categories with zero sales.✓ Correct
  • C. Create an inactive relationship between Products and Sales and activate it using USERELATIONSHIP in a measure.
  • D. Mark the Products table as a dimension table in the model settings.
  • E. Replace the Products[Category] column with a calculated column that uses RELATED to pull the category from Sales.
Explanation

B is correct: adding a measure such as [Sales Count] = COUNTROWS(Sales) and applying a visual-level filter (keep rows where Sales Count > 0) is the safest, most controlled approach to show only categories with sales. It avoids the performance and ambiguity risks of bidirectional filtering. A is wrong: enabling bidirectional cross-filtering would allow the Sales table to filter the Products table, which would indeed hide categories with no sales — but bidirectional filtering can cause ambiguity, unexpected results in other visuals, and performance degradation; it is not the recommended approach for this scenario. C is wrong: creating an inactive relationship and activating it via USERELATIONSHIP affects measure calculations, not visual filtering of dimension rows. D is wrong: there is no 'mark as dimension table' setting in Power BI Desktop that affects cross-filter behavior or row visibility. E is wrong: RELATED is used in calculated columns to look up values across a relationship in row context; pulling Category from Sales would break the data model logic, as Category belongs in Products, not Sales.

63 more questions in this domain

Practice the full bank with instant grading, flashcards, and a timed mock exam.

Start practicing free