cancel
Showing results for 
Search instead for 
Did you mean: 
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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.