RANKX & ALL giving two different values

DAX DAX Calculations
1

I have tried to understand the logic with RANKX and ALL. See the DAX below plus the screenshots also. Why does it give two different values?

image
image1294×596 43.5 KB

Top Customer Rig Count =
SUMX (
TOPN(1,
VALUES(‘Rig report by Basin’[Customer Name]),
[Total Rig Count]),[Total Rig Count])

Top Performing Customers =
IF (
ISFILTERED(‘Rig report by Basin’[Customer Name]),
RANKX(
ALL(‘Rig report by Basin’[Customer Name]),
[Total Rig Count],
ASC,
Dense),
BLANK()
)

Everything is Filtered by the slicer of week starting for the current month. I believe the Top Performing DAX gives the correct values.

Thanks, Paul

2

@Paul.Gerber ,

I wouldn’t expect these to return the same values, since TOPN and RANKX are doing very different things. In your first measure, TOPN is returning a one row table (or multiple rows if there are ties based on the specified criterion) with the highest rig count for that customer. On the other hand, RANKX is returning the rank of each customer across all customers based on total rig count.

What further confounds the comparison is in the first measure, customer name is subject to the filtering from all the slicers related via the data model. However in the second measure you are removing the filters from customer name via the ALL statement. I think what you may want in that measure is ALLSELECTED, rather than ALL. However diagnosing DAX without seeing the data model or the data is always a little dicey.

I hope this is helpful.

– Brian

1 Like
3

Thank you Brian. Makes total sense.