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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
SensingFailure
Frequent Visitor

Dynamic Category Switching with Ranking Measures

I have a single table with user logon durations that looks as such:

UserDateNormal Hours DurationAfter Hours DurationOffice 1Office 2Office 3
Keith1/22/224.22.5CT/TDCT/TD/TSICT/TSI
Amanda6/4/215.11.4DS/DRL/GADSDS/DRL
Beverly5/9/223.44.3DRL/WHADRL/PPDDRL

 

This table is being summarized through measures to look like:

Normal HoursAfter HoursNormal RankAfter Hours RankOffice 1Office 2Office 3
20.51.232CT/TDCT/TD/TSICT/TSI
44.115.211DS/DRL/GADSDS/DRL
31.5123DRL/WHADRL/PPDDRL

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.

SensingFailure_0-1659724385391.png

SensingFailure_1-1659724484514.png

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.

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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:

 

DataInsights_0-1659885734923.png

-----

 

DataInsights_1-1659885748783.png

-----

 

DataInsights_2-1659885760136.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

3 REPLIES 3
SensingFailure
Frequent Visitor

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

Glad to hear that worked!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




DataInsights
Super User
Super User

@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:

 

DataInsights_0-1659885734923.png

-----

 

DataInsights_1-1659885748783.png

-----

 

DataInsights_2-1659885760136.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

April Fabric Community Update

Fabric Community Update - April 2024

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