Q01 - Question
A measure contains a long expression with several nested calculations and returns an unexpected value. During design, you want to check one intermediate result without rewriting the whole formula. What should you do?
Domain: Model the data (25–30%) Type: Single choice
- A. Create a calculated column that stores the intermediate result.
- B. Declare the intermediate calculation as a variable and return that variable in the RETURN clause.
- C. Group the intermediate operations within parentheses to override the evaluation order.
- D. Create a calculated table that contains the intermediate result.
B is correct.
Explanation: When you declare at least one variable, the RETURN clause defines the expression that refers to the variables. Returning the variable of interest lets you test a complex formula at design time.
A is incorrect: A calculated column is evaluated for each table row and adds a column to the model. It is not the recommended way to test an intermediate value in a measure formula.
C is incorrect: Grouping operations within parentheses overrides the evaluation order (operator precedence), but it does not isolate and return an intermediate result for testing.
D is incorrect: A calculated table must return a table object and adds a table to the model. It does not isolate an intermediate scalar value in the measure.
Q02 - Question
A report consumer must use a slicer to enter a hypothetical currency exchange rate. Measures in the model must then use the selected rate in a calculation. The solution must not change the original data. What should you add to the model?
Domain: Model the data (25–30%) Type: Single choice
- A. A Numeric range parameter
- B. A Fields parameter
- C. A one-to-one relationship between the Sales table and a new exchange rate table
- D. A hierarchy on the Sales table
A is correct.
Explanation: A Numeric range parameter has a numeric data type, minimum and maximum values, an increment, and a default value. Power BI Desktop creates a calculated table and a measure that returns the selected value. The table has no relationship to other model tables, so it is a disconnected table. A slicer on the table supports a what-if scenario such as a hypothetical exchange rate.
B is incorrect: A Fields parameter lets report consumers choose which field to visualize. It doesn't provide a range of numeric values for a what-if calculation.
C is incorrect: The table that supports a numeric range parameter doesn't have or need a relationship to another model table. A one-to-one relationship also doesn't provide a selectable range of values.
D is incorrect: A hierarchy provides a navigation path across columns in a table. It doesn't let a consumer select a numeric value for a measure to use.
Q03 - Question
The Sales table in your semantic model has three relationships to the Date table: order date, ship date, and due date. Report authors need to filter sales by ship date by using a separate Date table that has its own active relationship. You accept that the model size and refresh time might increase. What should you do?
Domain: Model the data (25–30%) Type: Single choice
- A. Add a calculated column to Sales that returns the ship date.
- B. Create a measure that returns the ship date.
- C. Create a calculated table that duplicates the Date table.
- D. Set the Summarization property of the Date column to Don't summarize.
C is correct.
Explanation: A calculated table is created with a DAX formula that returns a table object, allowing you to duplicate the Date table. The duplicate can then have its own active relationship for ship-date filtering.
A is incorrect: A calculated column returns a single value for each row. It does not create a second date table that can have its own active relationship.
B is incorrect: A measure must return a single value and is calculated at query time. It does not add a table to the model.
D is incorrect: The Summarization property controls how a column is summarized in visuals. It does not create another table or relationship.
Q04 - Question
You publish an import model to a shared capacity. The compressed model size now exceeds 1 GB, and scheduled refresh fails. You must reduce the model size and keep the import framework. Which action should you take?
Domain: Model the data (25–30%) Type: Single choice
- A. Replace Power Query custom columns with calculated columns in the model.
- B. Increase the scheduled refresh frequency to 48 times per day.
- C. Enable auto date/time for all date columns.
- D. Remove unnecessary columns and rows, and group by and summarize fact tables to raise the grain.
D is correct.
Explanation: Shared capacity limits a dataset to 1 GB, and the limit applies to the compressed size of the model. Removing unnecessary columns and rows reduces the data stored in tables. Summarizing fact tables to a higher grain also reduces the stored data. Both techniques reduce refresh duration and speed up queries.
A is incorrect: Data reduction guidance prefers custom columns in Power Query instead of calculated columns in the model. This change does not reduce the stored data.
B is incorrect: Refresh frequency does not change the model size. A shared capacity also allows up to eight scheduled refreshes per day. Up to 48 per day applies to a dedicated capacity.
C is incorrect: Data reduction guidance recommends that you disable auto date/time. Enabling it does not reduce the model size.
Q05 - Question
The Sales table in an Import model has two date columns, OrderDate and ShipDate. Report authors must be able to filter by order date, ship date, or both at the same time. The model currently has one Date table. What should you do?
Domain: Model the data (25–30%) Type: Single choice
- A. Create two active relationships from the Date table to the Sales table, one for each date column.
- B. Create separate Order Date and Ship Date tables and create an active relationship from each table to the Sales table.
- C. Keep one Date table with one active relationship and one inactive relationship, and use the inactive relationship for ship date filtering.
- D. Set the cross filter direction to both on the two relationships between the Date table and the Sales table.
B is correct.
Explanation: There can be only one active filter propagation path between two model tables. To filter by order date, ship date, or both at the same time, create two date tables. Each table can then have an active relationship to the Sales table.
A is incorrect: Only one relationship between the Date table and the Sales table can be active. You can't set both as active.
C is incorrect: An inactive relationship becomes active only during the evaluation of a model calculation, by using the USERELATIONSHIP DAX function. It doesn't let report authors filter by both dates at the same time.
D is incorrect: Cross filter direction controls how filters propagate. It doesn't change the limit of one active relationship path between two tables.
Q06 - Question
A finance analyst needs a DAX function that returns the dates from the start of the current quarter through the current filter context, for use in a quarter-to-date calculation. The model already has a valid date table that is marked as a Date table. Which function should the analyst use?
Domain: Model the data (25–30%) Type: Single choice
- A. DATESYTD
- B. DATESMTD
- C. FIRSTDATE
- D. DATESQTD
D is correct.
Explanation: DATESQTD returns the dates for quarter-to-date in the current filter context. This matches the requirement for the start of the quarter through the current context.
A is incorrect: DATESYTD returns year-to-date dates. The range would start at the beginning of the year, not the quarter.
B is incorrect: DATESMTD returns month-to-date dates. The range would start at the beginning of the month, not the quarter.
C is incorrect: FIRSTDATE returns the first date in the current filter context for a date column. It does not return a quarter-to-date table of dates.
Q07 - Question
Report authors need a year-to-date (YTD) sales value in their visuals. The data modeler does not have DAX skills. Which approach meets the requirement?
Domain: Model the data (25–30%) Type: Single choice
- A. Set the Summarize by property of the Sales Amount column to Sum.
- B. Create a hierarchy on the Date table that has Year, Quarter, and Month levels.
- C. Create a Numeric range parameter and use its measure as the YTD value.
- D. Create a measure by using Quick measures or Copilot for Power BI.
D is correct.
Explanation: Report authors can summarize any column in a visual, but they can't achieve a complex summarization such as YTD that way. You need a measure. If you don't have DAX skills, you can use Quick measures, where you select a calculation template and drag fields to configure it. You can also use Copilot for Power BI to generate a measure from a prompt.
A is incorrect: Summarize by sets a default summarization for a numeric column, such as Sum. It doesn't produce a YTD calculation.
B is incorrect: A hierarchy provides a navigation path across columns in a table. It doesn't calculate a summarized value.
C is incorrect: A Numeric range parameter supports what-if scenarios by using a disconnected table and a selected value. It isn't designed to calculate YTD sales.
Q08 - Question
You write a measure that uses CALCULATE to evaluate [Total Sales]. You pass two Boolean filter expressions to CALCULATE: one that filters Product[Color] to Red, and one that filters Product[Category] to Bikes. How does CALCULATE combine these two filters when it evaluates the expression?
Domain: Model the data (25–30%) Type: Single choice
- A. It uses the OR logical operator, so the result includes products that are Red or in the Bikes category.
- B. It applies only the first filter and ignores the second filter.
- C. It uses the AND logical operator, so the result includes only products that are Red and in the Bikes category.
- D. It returns an error because CALCULATE accepts only one filter expression.
C is correct.
Explanation: CALCULATE accepts an expression and as many filters as you need. When you pass multiple filters, they are evaluated by using the AND logical operator. The measure therefore evaluates [Total Sales] only for products that meet both conditions.
A is incorrect: Multiple filters passed to CALCULATE are combined with AND, not OR. To return Red or Bikes products, you would need different filter logic.
B is incorrect: CALCULATE evaluates all filters that you pass in. It does not discard the second filter.
D is incorrect: CALCULATE accepts as many filters as you need. Boolean expressions are valid filters.
Q09 - Question
You create a measure that calls the DIVIDE function with a numerator expression and a denominator expression. You do not pass an alternate result. For some products, the denominator is zero. What does the measure return for those products?
Domain: Model the data (25–30%) Type: Single choice
- A. BLANK
- B. 0
- C. An error value
- D. The value of the numerator
A is correct.
Explanation: The DIVIDE function can return an alternate result. If the denominator is zero or BLANK and you do not pass an alternate result, DIVIDE returns BLANK.
B is incorrect: To return a specific value for these products, you must pass it as the alternate result. Without an alternate result, the function returns BLANK, not 0.
C is incorrect: The DIVIDE function handles division by zero gracefully by returning BLANK. It does not return an error value.
D is incorrect: DIVIDE does not return the numerator when the denominator is zero. It returns BLANK unless you pass an alternate result.
Q10 - Question
A retail company wants a report that counts new occurrences in its fact data, such as customers who made their first purchase in each period. Which type of time intelligence calculation fits this requirement?
Domain: Model the data (25–30%) Type: Single choice
- A. Life-to-date (LTD)
- B. Year-over-year (YoY)
- C. Year-to-date (YTD)
- D. Snapshot filtering to a single value
A is correct.
Explanation: Life-to-date (LTD) calculations help you count new occurrences in your fact data. A new customer is counted in the period of their first purchase.
B is incorrect: Year-over-year (YoY) calculations compare a period with the same period in the prior year. They do not identify first occurrences.
C is incorrect: Year-to-date (YTD) calculations accumulate values from the start of the year. They do not count first occurrences.
D is incorrect: Snapshot filtering ensures that only a single snapshot value is returned, such as for inventory on-hand. It does not count new occurrences.