Rank by hierarchy in a mtrix table

[Qotsa]

Hi,

Need to rank 'Location' which is the 2nd hierarchy. 1st hierarchy is 'Group CHO1' which ranks correctly.

This is what I have and it ranks the 1st hierarchy correctly but not the 2nd is all 1.

Ranks By Hierarchy = IF (

ISFILTERED (Query1[Group CHO1]),

rankx(all(Query1[Group CHO1]),[Last 8 by Con])

, IF(

ISFILTERED (Query1[Location]),

rankx(all(Query1[Location]),[Last 8 by Con])))

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Ranks By Hierarchy = IF(HASONEVALUE(Query1[Location]),rankx(all(Query1[Location]),[Last 8 by Con]),rankx(all(Query1[Group CHO1]),[Last 8 by Con]))

[Jihwan_Kim · Accepted solution]

Hi, @Qotsa 

Please check the below picture and the measure.

I tried to create a sample similar to the case.

I am not sure how your whole data model looks like, but I suggest "defining the lower level hierarchy's rank first" in the measure is critical.

Rank by Hierarchy =

SWITCH (

TRUE (),

ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table'[Product] ), [Qty total] ),

RANKX ( ALL ( 'Table'[Group] ), [Qty total] )

)

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 · Accepted solution]

Hi, @Qotsa 

Please check the below picture and the measure.

I tried to create a sample similar to the case.

I am not sure how your whole data model looks like, but I suggest "defining the lower level hierarchy's rank first" in the measure is critical.

Rank by Hierarchy =

SWITCH (

TRUE (),

ISINSCOPE ( 'Table'[Product] ), RANKX ( ALL ( 'Table'[Product] ), [Qty total] ),

RANKX ( ALL ( 'Table'[Group] ), [Qty total] )

)

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

[Qotsa · Accepted solution]

@Jihwan_Kim Your measure works. Grouping thelower level first is key it seems.

Thank you. 

[Ashish_Mathur · Accepted solution]

Hi,

Does this measure work?

Ranks By Hierarchy = IF(HASONEVALUE(Query1[Location]),rankx(all(Query1[Location]),[Last 8 by Con]),rankx(all(Query1[Group CHO1]),[Last 8 by Con]))

[Qotsa]

@Ashish_Mathur Your measure works. Grouping by the lower level first it seems is the way to do it.

Thank you.

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.