topic Re: Calculate Rank based on more tables in DAX Commands and Tips

Calculate Rank based on more tables

soldous — Wed, 29 Jan 2020 19:39:17 GMT

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

Re: Calculate Rank based on more tables

JarroVGIT — Wed, 29 Jan 2020 20:14:22 GMT

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

Re: Calculate Rank based on more tables

JarroVGIT — Wed, 29 Jan 2020 21:07:28 GMT

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

Re: Calculate Rank based on more tables

soldous — Wed, 29 Jan 2020 21:10:34 GMT

Thank you very much Djerro!!

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

Re: Calculate Rank based on more tables

JarroVGIT — Fri, 31 Jan 2020 18:37:24 GMT

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:

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