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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.