Forum Discussion
Using RANKX, with a measure, and ISINSCOPE, within a matrix hierarchy
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
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
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
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