Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
The output currently looks like:
ALL REGIONS (Top Level)
"ATLANTIC" Region (Drill-Down)
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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?
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |