topic Re: Rankx Value by category hierarchy for each dynamic time period in matrix in DAX Commands and Tips

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

Anonymous — Fri, 18 Mar 2022 15:25:34 GMT

Im trying to get this

but i get this

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

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

Anonymous — Fri, 18 Mar 2022 18:03:47 GMT

Hi Anonymous , could you please upload a sample PBIX file?

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

Anonymous — Fri, 18 Mar 2022 19:38:44 GMT

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.

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.

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