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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Missy
Frequent Visitor

Rankx Value by category hierarchy for each dynamic time period in matrix

Im trying to get this 

Missy_0-1647616654964.png

but i get this 

Missy_1-1647617102262.png

 

here is my current code 

RankbyDate =
VAR Region = ISFILTERED(Station_Departure[xRegion_t])
VAR SubRegion = ISFILTERED(Station_Departure[xSubRegion_t])
VAR Station = ISFILTERED(Station_Departure[xStn_t])
VAR Result = SWITCH ( true(),
Region&&NOT(SubRegion)&&NOT(Station),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xRegion_t]), [TotalAgreementLevelScore],,DESC)),
SubRegion&&NOT(Station)&&NOT(Region),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xSubRegion_t]), [TotalAgreementLevelScore],,DESC)),
Station&&NOT(Region)&&NOT(SubRegion),CALCULATE (RANKX(ALLSELECTED(Station_Departure[xStn_t]), [TotalAgreementLevelScore],,DESC)))

RETURN Result
2 REPLIES 2
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Hi @Missy , could you please upload a sample PBIX file?

Unfortunately I cant but I can give you a picture of the data

I have a Date Table that connects to Event Dates in my “Base” table that has unique responses by ID. Then that table connects to all the responses per unique id per question.  And each unique ID is connected to a meta data table which connects to the category hierarchy.  As shown below.

Missy_0-1647632312228.png

 

 

I then have some measures that calculate a score based on various answers to varies questions

TotalAgreementLevelScore =

….. VARS

VAR TotalAgreementLevelScore = CALCULATE(TotalAgreementLevelMultiplierCount/TotalAgreementLevelCount)

RETURN TotalAgreementLevelScore

 

Then I calculate the rank based on score by Category Hierarchy

VAR Region = ISINSCOPE(Station_Departure[xRegion_t])

VAR SubRegion = ISINSCOPE(Station_Departure[xSubRegion_t])

VAR Station = ISINSCOPE(Station_Departure[xStn_t])

VAR Result = SWITCH ( true(),

    Region&&NOT(SubRegion)&&NOT(Station),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xRegion_t]), [TotalAgreementLevelScore],,DESC)),

    SubRegion&&NOT(Station)&&NOT(Region),CALCULATE( RANKX(ALLSELECTED(Station_Departure[xSubRegion_t]), [TotalAgreementLevelScore],,DESC)),

    Station&&NOT(Region)&&NOT(SubRegion),CALCULATE (RANKX(ALLSELECTED(Station_Departure[xStn_t]), [TotalAgreementLevelScore],,DESC)))

 

RETURN Result

 

Then put the results into a matrix to show each category rank by time period and it does not work.

 

Missy_1-1647632312234.png

 

 

Also when I try to expand the rows I end up with no results for the sub categories

Missy_2-1647632312236.png

 

 

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors