RANKX measure skips in hierarchy - using measure

Raado12 · ‎01-29-2021

Hello,

I have a problem with RANKX function. Now I have a formula:

RANKX(ALL(clinics[region]),[%To Goal],,,Dense)

I have already tried combination with function IF which include function ISFILTERED on a column region of table clinics.

Matrix has two rows, one (upper) is region and one (lower) level is office. I want to calculate rank of region (the upper rows in hierarchy) based on the values in column %Goal. The results are a bit weird, the ranks are skipped and I get rank of 1 and 4, but I have only two regions. Can someone help me?

wdx223_Daniel · ‎01-29-2021

RANKX(ALL(clinics[region]),calculate([%To Goal],all(office)),,,Dense)

CNENFRNL · ‎01-29-2021

Hi, @Raado12 , as to your measure

RANKX(ALL(clinics[region]),[%To Goal],,,Dense)

the rank evaluates among all items in clinics[region] column, which results in the seemingly weird result.

Generally speaking, hierarchical rank requires ISINSCOPE() to distinguish level it resides in. You may want to refer to this article for more details.

Should you have further questions or some other issues, it's better to attach a dummy file with mockup data for futher disscusion.

Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!