Calculate Rank based on more tables

Hi soldous ,

THank you for sharing your pbix. I won't share it back here but I do want to shar ethe solution as it might inspire others on similar questions. 
To be honest, I don't know if this is the best solution to this problem but it works (and that is better then what you currently have, I suppose :P)

THe measure is:

Rank = 
VAR _tmpTable = SUMMARIZE(ALL(FactFeedback), DimModelYear[Year], DimModel[ModelCategory], "sentiment", [Senstiment])
VAR _curYear = SELECTEDVALUE(DimModelYear[Year])
VAR _curModel = SELECTEDVALUE(DimModel[ModelCategory])
VAR _rankedTable = ADDCOLUMNS(FILTER(_tmpTable, [ModelCategory] <> BLANK()), "rank", 
    VAR _curTmptableYear = [Year]
    RETURN
    RANKX(FILTER(_tmpTable, [Year]=_curTmptableYear), [sentiment],,ASC,Dense))
RETURN
MAXX(FILTER(_rankedTable, [Year] = _curYear && [ModelCategory] = _curModel), [rank])

I will have to explain tomorrow what is happening here because I have to sleep, but here is a screengrab of your matrix where it is in action. If you want the rank to be reverserd, change ASC into DESC in the above measure 🙂

Like I said, I looooove ranking questions, they are a lot of fun 😄

Have a good night!

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

So soldous aproached me with a very valid question; this measure will not recalculate the rank based on a filtered selection of Models. The key bit here is the ALL() statement in the first argument of SUMMARIZE(). This removes any filters applied to the query FactFeedback. If you create a slicer on DimModel[ModelCategory], that will implicitely filter the FactFeedback query. Then using ALL will result in that Model8 in 2020 always have rank=5, regardless of the selected models in the DimModel[ModelCategory] slicer. 

This can be circumvented by replacing ALL by ALLSELECTED(). This removes any filters of inside the query (so inside the measure), but retains all filters from outside the measure (for example, a filter caused by a slicer). The result is  this:

Using All, the current slicer selection returns ranks of 2, 3 and 7 in 2020.Using ALLSELECTED() returns the newly calculated ranks as we want them.

So, for completion here is the measure using ALLSELECTED:

Rank = 
VAR _tmpTable = SUMMARIZE(ALLSELECTED(FactFeedback), DimModelYear[Year], DimModel[ModelCategory], "sentiment", [Senstiment])
VAR _curYear = SELECTEDVALUE(DimModelYear[Year])
VAR _curModel = SELECTEDVALUE(DimModel[ModelCategory])
VAR _rankedTable = ADDCOLUMNS(FILTER(_tmpTable, [ModelCategory] <> BLANK()), "rank", 
    VAR _curTmptableYear = [Year]
    RETURN
    RANKX(FILTER(_tmpTable, [Year]=_curTmptableYear), [sentiment],,ASC,Dense))
RETURN
MAXX(FILTER(_rankedTable, [Year] = _curYear && [ModelCategory] = _curModel), [rank])

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂