Dynamic Category Switching with Ranking Measures

[SensingFailure]

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.

[SensingFailure]

This took care of it wonderfully!  Thank you so much!  I never knew about the ISINSCOPE function.

[DataInsights]

Glad to hear that worked!

[DataInsights · Accepted solution]

@SensingFailure,

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:

-----

-----