Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MPICKETT
Frequent Visitor

Using RANKX, with a measure, and ISINSCOPE, within a matrix hierarchy

Hello - 

 

I have tried everything I can think of or could find content for, in order to understand how to achieve the proper rankings at each level in a two-level hierarchy but I am stumped and frustrated.

 

Issue:

 

I have a Measure, Total Hours per Driver, that I am trying to use in the calculation of another Measure, Total Hours per Driver (rank). I would like the Rank Measure to provide a 1-10 ranking for Regions at the top of the hierarchy, then provide a ranking of 1-20 for the individual Drivers once I drill-down into each Region.

 

As it stands, I can achieve the proper ranking at the Driver level, but not the Regional level.

 

The current formula looks as such:

 

Total Hours per Driver (rank) =

 

VAR IsRegionVisible =

ISINSCOPE('Region Week Range Key'[Region])

 

VAR IsDriverVisible =

ISINSCOPE('Region Week Range Key'[Driver ID])

 

VAR DriverMeasure =

RANKX(ALLSELECTED('Region Week Range Key'[Driver ID]), CALCULATE([Total Hours per Driver (measure)]),,DESC)

 

VAR RegMeasure =

RANKX(ALLSELECTED('Region Week Range Key'[Region]), CALCULATE([Total Hours per Driver (measure)]),,DESC)

 

RETURN

 

SWITCH(TRUE(),

IsDriverVisible, DriverMeasure,

IsRegionVisible, RegMeasure,

BLANK())

 

There is a relationship between two tables connected by a key that looks like:

 

Hierarchy Ranking Relationships.JPG

 

The output currently looks like:

 

ALL REGIONS (Top Level)

RegionalRankError.png

"ATLANTIC" Region (Drill-Down)

DriverRankSuccess.png

My goal would be to keep the Atlantic Region ranking as is, which is correct but adjust the Regional rankings so that they reflect 1-8 rankings.

 

Thank you so much for your time and assistance.

 

Best,

 

MP

 
1 ACCEPTED SOLUTION

You read correctly that a CALCULATE() is needed there; however, when you reference a measure like that, it is automatically wrapped in one.  If you were to write SUM(Table[Column1]), that would need to be wrapped in CALCULATE().  

 

For your measure, to troubleshoot, do you get the expected result for region rank when you take driver out of the hierarchy?  Or if you comment out your return with // and return your isdrivervisible and isregionvisible variables instead, you can help diagnose where the problem is.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

6 REPLIES 6
Anonymous
Not applicable

Hi all, Im actually facing an issue where I have a list of hierarchy.

 

Country>Customer>Division>Product

 

My measure is a switch function where if the slicer selected is sales, it will show sales amount, if cost, it will show cost amount.

 

However, i need to see top 20 by customer. the hierarchy should also only reflect on the top 20 customer.

 

for example from the top 20 customer, we will know these top 20 customer is coming from which country, what division they have what product they have..

 

is there a dax for this that i can refer to?

 

mahoneypat
Employee
Employee

A couple comments/questions to consider:

 

1. Why are you wrapping the measures inside a CALCULATE()?  I don't think that is necessary and could be the problem.

2. What do you mean 1-8 vs 1-20?  The rank will be based on how many rows are in the ALLSELECTED() tables.  Do you mean there are 8 regions and 20 drivers, so that is what you expect?

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

1. I read in a different thread that for a Measure to function appropriately in RANKX, it needed to be wrapped in CALCULATE(), but I just removed the CALCULATE() and it provides the same result as before.

 

2. Sorry for the lack of clarity, you are correct. My ranking expectation is based on there being 8 Regions and each Region has on average 20 Drivers, which is where the reference to those values came from.

You read correctly that a CALCULATE() is needed there; however, when you reference a measure like that, it is automatically wrapped in one.  If you were to write SUM(Table[Column1]), that would need to be wrapped in CALCULATE().  

 

For your measure, to troubleshoot, do you get the expected result for region rank when you take driver out of the hierarchy?  Or if you comment out your return with // and return your isdrivervisible and isregionvisible variables instead, you can help diagnose where the problem is.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@mahoneypat 

Thank you for your help. I feel like an idiot though, lol.

 

I tried to isolate just the Region field earlier and I did not receive the expected result. However, I took your advice and tried to isolate it again, but this time I realized that I did not heed the warning I read from an earlier post... be careful of the effects from Page Filters with this calculation. I cleared the Page Filter and adjusted the formula to calculate with the condition of the Hours per Driver measure not being BLANK, and voila, I am now receiving the expected results.

 

Thank you for your assistance and time. I very much appreciate it.

 

Best,

MP 

Glad it worked out.  I did something like that even today.

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.