CODEWITHSUNDEEP
powerbidax
IanJay
IanJay

Reputation: 393

RANKX() issues in DAX, PowerBI

I am learning DAX and confused about the RANKX() in PowerBI. Here is my data:

enter image description here

And here is my measure:

Rank = RANKX(
    ALL(RankDemo[Sub Category]),
    CALCULATE(SUM(RankDemo[My Value])))

Here is my visual:

enter image description here

The RANKX() works fine, but the field [My Value] has to be summed in the PowerBI field setting: enter image description hereenter image description here

If I choose Don't Summarize, the rank will be all 1. Anyone could explain this? What does the Sum have to do with the RANKX() or CALCULATE() in DAX. Thanks.

Upvotes: 4

Views: 9509

Answers (1)

RADO
RADO

Reputation: 8148

The problem you are experiencing has nothing to do with RANKX. The issue is with what's called "Implicit measure" - an (unfortunately) common bad practice in Power BI and Power Pivot.

Numeric fields in Power BI can serve 2 roles:

  • they can be inputs into DAX measures (such as SUM() etc)
  • or they can be filters (i.e, serve the same function as "Sub Categories" in your visual).

When you drop 'My Value" into a table without any calculation ("Don't summarize"), you are telling Power BI that you want "My Value" to serve as a filter. In Excel pivot tables, it would be equivalent to dropping "My Value" into "Rows" area instead of "Values". So each row in your table is now grouped by "Subcategory + My Value", not just "Subcategory" (in other words, you've made "My Value" a part of your filter context). Since each combination of "Subcategory + My Value" is unique, you are essentially ranking tables consisting of 1 record (that's why it always returns 1).

When you choose "SUM" for "My Value", it's no longer a row filter - it's now a measure. So you filter context now is not "Subcategory" + "My Value", but just "Subcategory", and your RANKX formula works properly. You can easily see this by removing summed "My Value" from the table - RANKX measure will still work the same way.

When you use this "SUM" aggregation for "My Value", you are telling Power BI to create a DAX measure for you implicitly (that's why it's called "implicit measure"). It happens whenever you drop a numeric field into a visual directly. Such implicit measures are considered a bad practice among experienced designers, for a number of reasons, for example:

  • it's confusing (your trouble with RANKX is a typical example);
  • you can't re-use implicit measures (can't refer to them in other DAX measures).

A solution is:

  • Never, ever drop numeric fields into visuals directly.
  • Instead, always write a DAX measure, and then drop it into a visual.

In your example, I would create an explicit DAX measure:

Total Value = SUM(RankDemo[My Value])

Now you can use it everywhere in your model. You can drop it into a visual to see sum of My Value. Or you can use it in your RANKX measure:

Rank = RANKX( ALL(RankDemo[Sub Category]), [Total Value])

The benefits of such design are:

  • No hidden effects (you know exactly what [Total Value] does)
  • You can use [Total Value] in many other formulas without need to write summation again and again.
  • If you change DAX in [Total Value] (for example, add rounding), it will automatically update all other formulas where it's used.
  • Re-using DAX measures makes formulas cleaner, easier to understand.

Upvotes: 9

Related Questions