Forum Discussion

rbalza's avatar
rbalza
Icon for Helper III rankHelper III
5 years ago
Solved

Rank based on slicer with two rows

Hi Everyone,  I have created a slicer which everytime you hit, the chart will automatically update based on its rank. It works fine, however, when I add another data on the rows, the slicer doesn't ...
Need Help DAX
Need Help DAX measure
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, rbalza 

    Thank you for your feedback.

    Please check the link down below. I tried to create a sample pbix file based on your explanation.

    I assume there is a relationship between tables, right?

     

    If it still does not suit your case, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

    Thanks.

     

    https://www.dropbox.com/s/7d9ydrad47al8y0/rbalza.pbix?dl=0 

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, rbalza 

    I am not sure you checked my revised pbix file.

     

    please check the link in my last message.

     

    In the latest one, the measure is not like above.

    it is like below.

     

    Rank by Group Owner and Invoice Type =
    RANKX(ALL(Data), [Total Invoice],,DESC)
     

    Hi, My name is Jihwan Kim.


    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

Hi, rbalza 

I am not sure how your data model looks like, but in my opinion, if you want to add more columns into the context, the rank measure and ranking dimension inside the VAR have to be changed.

I created a simple sample pbix file, and please check the below picture and the link down below.

All measures are in the sample pbix file.

I hope you can catch how to change yours.

Or, if you can share your sample pbix file, then I can try to look into it to come up with more accurate measures.

 

 

Rank by Group Owner =
RANKX(ALL(Data[Group Owner]), [Total Invoice],,DESC)
 
Rank by Group Owner and Invoice Type =
RANKX(ALL(Data[Group Owner],Data[Invoice Type]), [Total Invoice],,DESC)
 
Invoice total by rank group =
CALCULATE (
[Total Invoice],
FILTER (
CROSSJOIN ( VALUES ( Data[Group Owner] ), VALUES ( Data[Invoice Type] ) ),
COUNTROWS (
FILTER (
'Group',
[Rank by Group Owner and Invoice Type] >= 'Group'[Min]
&& [Rank by Group Owner and Invoice Type] <= 'Group'[Max]
)
) > 0
)
)
 
 
 

Hi, My name is Jihwan Kim.


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

  • rbalza's avatar
    rbalza
    Icon for Helper III rankHelper III
    5 years ago

    Hi Jihwan_Kim I am attempting to do your measure however, my group owner and invoice type has a different table and it shows an error since ALL function needs to be on the same table. What is the recourse for this? Thanks!

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      5 years ago

      Hi, rbalza 

      Thank you for your feedback.

      Please check the link down below. I tried to create a sample pbix file based on your explanation.

      I assume there is a relationship between tables, right?

       

      If it still does not suit your case, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

      Thanks.

       

      https://www.dropbox.com/s/7d9ydrad47al8y0/rbalza.pbix?dl=0 

       

      Hi, My name is Jihwan Kim.

       

      If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

       

      Linkedin: linkedin.com/in/jihwankim1975/

      Twitter: twitter.com/Jihwan_JHKIM

      • rbalza's avatar
        rbalza
        Icon for Helper III rankHelper III
        5 years ago

        Hi Jihwan_Kim, it is pretty similar to what you have done. I checked your pbix sample and  separated the invoice type in another table and an error is showing.