PL-300 · Question #73
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer…
The correct answer is A. Create a calculated measure that uses the COUNTA(Order_ID) DAX formula. To display the total number of orders by month and year, create a calculated measure that uses the COUNTA DAX function on the Order_ID column.
Question
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series. You have a Microsoft SQL Server database that contains the following tables. The following columns contain date information:
- Date[Month] in the mmyyyy format
- Date[Date_ID] in the ddmmyyyy format
- Date[Date_name] in the mm/dd/yyyy format
- Monthly_returns[Month_ID] in the mmyyyy format
The Order table contains more than one million rows. The Store table has a relationship to the Monthly_returns table on the Store_ID column. This is the only relationship between the tables. You plan to use Power BI Desktop to create an analytics solution for the data. You are modifying the model to report on the number of orders. You need to calculate the number of orders. What should you do?
Options
- ACreate a calculated measure that uses the COUNTA(Order_ID) DAX formula.
- BCreate a calculated column that uses the COUNTA(Order_ID) DAX formula.
- CCreate a calculated column that uses the SUM(Order_ID) DAX formula.
- DCreate a calculated measure that uses the SUM(Order_ID) DAX formula.
How the community answered
(34 responses)- A94% (32)
- B3% (1)
- C3% (1)
Why each option
To display the total number of orders by month and year, create a calculated measure that uses the COUNTA DAX function on the Order_ID column.
A calculated measure using `COUNTA(Order_ID)` is the correct approach to count the number of orders. Measures are dynamically evaluated in the report context (e.g., by month and year), providing the correct aggregated count for each slice of data without increasing the model size. `COUNTA` is appropriate for counting non-empty order IDs.
Creating a calculated column with `COUNTA(Order_ID)` would attempt to count all order IDs for *every row* in the table, resulting in a single, constant value for the entire column, which is incorrect for aggregation by month/year.
The `SUM(Order_ID)` DAX formula would add up the numerical values of the Order_IDs, which is meaningless for counting distinct orders and will produce an incorrect total.
Using `SUM(Order_ID)` as a measure is still incorrect because it sums the ID values rather than counting the occurrences of orders, yielding an irrelevant numerical sum instead of a count.
Concept tested: DAX measures for counting and aggregation
Source: https://learn.microsoft.com/en-us/dax/counta-function-dax
Topics
Community Discussion
No community discussion yet for this question.