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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
soldous
Advocate II
Advocate II

Calculate Rank based on more tables

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 

 

 

2 ACCEPTED SOLUTIONS

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 🙂

image.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
JarroVGIT
Resident Rockstar
Resident Rockstar

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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 🙂

image.png

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thank you very much Djerro!!

 

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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.