Forum Discussion
Dynamic Category Switching with Ranking Measures
I have a single table with user logon durations that looks as such:
| User | Date | Normal Hours Duration | After Hours Duration | Office 1 | Office 2 | Office 3 |
| Keith | 1/22/22 | 4.2 | 2.5 | CT/TD | CT/TD/TSI | CT/TSI |
| Amanda | 6/4/21 | 5.1 | 1.4 | DS/DRL/GA | DS | DS/DRL |
| Beverly | 5/9/22 | 3.4 | 4.3 | DRL/WHA | DRL/PPD | DRL |
This table is being summarized through measures to look like:
| Normal Hours | After Hours | Normal Rank | After Hours Rank | Office 1 | Office 2 | Office 3 |
| 20.5 | 1.2 | 3 | 2 | CT/TD | CT/TD/TSI | CT/TSI |
| 44.1 | 15.2 | 1 | 1 | DS/DRL/GA | DS | DS/DRL |
| 31.5 | 1 | 2 | 3 | DRL/WHA | DRL/PPD | DRL |
The users are being summarized into the office level using measures. The purpose behind the measures is to allow for analysis manipulation on the dashboard in the form of a date slicer and weighting parameters. The ranks are also calculated measures as they will change based on different weight selections.
Total Normal Use = SUMX( Citrix, Citrix[Normal Hours Use Only])Normal Use Rank = RANKX( ALLSELECTED( Citrix[Office 1]), Citrix[Total Normal Use],, DESC)
However, I also have a slicer that allows the user to select the office hierarchy they prefer (Office 1/2/3) and when selected, the respective column in the table visual changes. The summarization of user hours seems to transfer over just fine, but it is the ranking that I am struggling with. I have tried using just the table name instead of the column name, but it ranks every row as 1. I have also tried using field parameters to accomplish this with the same result. It only works when I call the specific office column within the rank measure.
Is there a way to accomplish this in DAX? To have the dimensional column change dynamically in a measure based on the selection of a slicer?
My last ditch effort will include making 3 tables with different measures and showing/hiding them based on a slicer, but I'm afraid it will significantly impact performance; I would prefer to avoid this if possible.
Unfortunately, the RANKX function requires specifying the column to be ranked. However, you can use SWITCH in a measure to specify each column:
Normal Use Rank = SWITCH ( TRUE, ISINSCOPE ( Citrix[Office 1] ), RANKX ( ALLSELECTED ( Citrix[Office 1] ), [Total Normal Use],, DESC ), ISINSCOPE ( Citrix[Office 2] ), RANKX ( ALLSELECTED ( Citrix[Office 2] ), [Total Normal Use],, DESC ), ISINSCOPE ( Citrix[Office 3] ), RANKX ( ALLSELECTED ( Citrix[Office 3] ), [Total Normal Use],, DESC ) )Create a fields parameter using the three Office fields:
Office Level = { ("Office 1", NAMEOF('Citrix'[Office 1]), 0), ("Office 2", NAMEOF('Citrix'[Office 2]), 1), ("Office 3", NAMEOF('Citrix'[Office 3]), 2) }Result:
-----
-----
3 Replies
Unfortunately, the RANKX function requires specifying the column to be ranked. However, you can use SWITCH in a measure to specify each column:
Normal Use Rank = SWITCH ( TRUE, ISINSCOPE ( Citrix[Office 1] ), RANKX ( ALLSELECTED ( Citrix[Office 1] ), [Total Normal Use],, DESC ), ISINSCOPE ( Citrix[Office 2] ), RANKX ( ALLSELECTED ( Citrix[Office 2] ), [Total Normal Use],, DESC ), ISINSCOPE ( Citrix[Office 3] ), RANKX ( ALLSELECTED ( Citrix[Office 3] ), [Total Normal Use],, DESC ) )Create a fields parameter using the three Office fields:
Office Level = { ("Office 1", NAMEOF('Citrix'[Office 1]), 0), ("Office 2", NAMEOF('Citrix'[Office 2]), 1), ("Office 3", NAMEOF('Citrix'[Office 3]), 2) }Result:
-----
-----
This took care of it wonderfully! Thank you so much! I never knew about the ISINSCOPE function.
Glad to hear that worked!
