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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors