PL-300 · Question #177
Note: This question is a part of a series of questions that present the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answ
The correct answer is B. Product Ranking= RANKX (ALL, (`Product'), [SalesAmount], , DESC, Dense). To rank total sales by product while considering all products in the ranking context and handling ties with consecutive ranks, the RANKX function with ALL('Product') and the Dense tie-breaking method is required.
Question
Note: This question is a part of a series of questions that present 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 exactly the same in each question in this series. Start of repeated scenario You have a Microsoft SQL Server database that has the tables shown in the Database Diagram exhibit. You plan to develop a Power BI model as shown in the Power BI Model exhibit. You plan to use Power BI to import data from 2013 to 2015. Product Subcategory [Subcategory] contains NULL values. End of repeated scenario. You implement the Power BI model. You need to add a measure to rank total sales by product. The results must appear as shown in the following table. Which DAX formula should you use?
Exhibit
Options
- AProduct Ranking= RANKX (Product, [SalesAmount], , DESC, Skip)
- BProduct Ranking= RANKX (ALL, (`Product'), [SalesAmount], , DESC, Dense)
- CProduct Ranking= RANKX (ALL, (`Product'), [SalesAmount], , DESC, Skip)
- DProduct Ranking= RANKX (ALL (`Product'), [SalesAmount], , Asc, Dense
How the community answered
(64 responses)- A11% (7)
- B81% (52)
- C5% (3)
- D3% (2)
Why each option
To rank total sales by product while considering all products in the ranking context and handling ties with consecutive ranks, the `RANKX` function with `ALL('Product')` and the `Dense` tie-breaking method is required.
Using `Product` without `ALL` would rank only within the current filter context, not across all products, and `Skip` for tie-breaking would result in non-consecutive ranks (e.g., 1, 2, 2, 4), which is often not desired.
The `RANKX` function calculates rank; `ALL('Product')` removes any existing filters on the 'Product' table to ensure ranking considers all products in the model. `DESC` sorts by sales from highest to lowest, and `Dense` assigns consecutive ranks even when there are ties (e.g., 1, 2, 2, 3), which is standard for such ranking scenarios.
Concept tested: DAX RANKX function for product ranking
Source: https://learn.microsoft.com/en-us/dax/rankx-function-dax
Topics
Community Discussion
No community discussion yet for this question.