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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors