ranking in matrix not working

[Anonymous]

Hi,

I have matrix and I am trying to caluate the ranking with hierachy, the dax works if the hierarchy is coming from the same table, and doesn't work if it is coming from another table.

1st level of Hierarchy

2nd level of Hierarchy

3rd level of Hierarchy

Below is the Dax I am using to create the rank function

UNIT Rank =

VAR IsDivisionFiltered =

ISINSCOPE ( Sites[Division] )

VAR IsSiteFiltered =

ISINSCOPE ( Sites[SiteName] )

VAR IsSalesExecFiltered =

ISINSCOPE ( Sales[Sales Exec] )

RETURN

SWITCH (

TRUE (),

AND (

AND ( IsDivisionFiltered, NOT ( IsSiteFiltered ) ),

CALCULATE ( [UNITS %] ) <> BLANK ()

), RANKX (

ALLSELECTED ( Sites[Division] ),

CALCULATE ( [UNITS %] ),

,

DESC,

DENSE

),

AND (

AND ( IsSiteFiltered, NOT ( IsSalesExecFiltered ) ),

CALCULATE ( [UNITS %] ) <> BLANK ()

), RANKX (

ALLSELECTED ( Sites[SiteName] ),

CALCULATE ( [UNITS %] ),

,

DESC,

DENSE

),

AND ( IsSalesExecFiltered, [UNITS %] <> BLANK () ), RANKX (

ALLSELECTED ( Sales[Sales Exec] ),

CALCULATE ( [UNITS %] ),

,

DESC,

DENSE

),

BLANK ()

)

any suggestions are very much appreciated

[v-yingjl]

Hi @Anonymous ,

Seems like a dynamic hierarchy rankx in matrix visual, you can rerfer these articles that could help you:

1. How to Create a Dynamic Rank in Matrix with Hierarchy? 
2. DAX – Ranking with Hierarchy Drill-Down 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi,

Thanks for your reply.

But I think there is a bug in Power BI i.e. the dax query I was using is working fine when I deleted the old matrix I was using and was working fine when I created a new matrix.

[harshnathani]

Hi @Anonymous ,

You can refer to this article.

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

Regards,

HN

[amitchandak]

@Anonymous , In case of rank If you create a rank on City and even add CIty Id it will not work. For Rank measure context is very important .

In this Example on 2nd and 3rd give correct Rank with Rank City and Citym which are the lowest level in my table

City Rank = RANKX(all(Geography[City]),[Sales]) // Do work with City ID
Geography Rank = RANKX(all(Geography),[Sales]) // Table is used
City and ID Rank = RANKX(all(Geography[City],Geography[City Id]),[Sales])

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Mariusz]

Hi @Anonymous 

Can you provide what you expected as a result?

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[Anonymous]

Hi,

This is how I am expecting the rank to work

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.