Calculate Rank based on more tables

[soldous]

Hi,

I have 3 related tables. 

• DimModel for all Item models
• DimModelYear for all years each model was created
• FactFeedback where models are rated
  • In this table, a measure sentiment is calculated based on good/bad rating

I need to calculated Rank for each model in each year based on the sentiment value.

Could you please help me with this DAX? I saw many RANKX examples but couldn't bend any to my needs.

Thousand thanks in advance.

Zdenek 

[JarroVGIT · Accepted solution]

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! 🙂

[JarroVGIT]

It is almost impossible to help you without knowing what your (relevant) data structure looks like. What columns are we talking about? Can you give a few lines of data of the FactFeedback table and the corresponding rank you expect? 

The best way would be sharing a PBIX with dummy (or real, if there is no confidential data!) data, is that possible? You can even PM me a link if you don't want to share publicly, I love RANKX questions 🙂

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! 🙂

[JarroVGIT · Accepted solution]

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! 🙂

[soldous]

Thank you very much Djerro!!

You are the best! Also if you had a better solution I would like to hear it. 🙂

[JarroVGIT · Accepted solution]

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! 🙂