RANKX() does not work properly when I put more rows on a matrix visualization

[johnyip]

Hello, I need to do some ranking on my data. Everything works fine as below except when I put an extra row in the marsix visaulization.

I need to have the result of RANKX() when there is only one row in the matrix, when I am indeed having 2 rows in the matrix. I believe I need to have the result stored in a separate measures (with the same value even after I expand the hierarchery) because I need this value to do some filtering.

It would be also very helpful to also have a measure that showcase the respective market ranking correctly for the sake of users.

Would be grateful if I can receive some help.

File: https://drive.google.com/file/d/1SWSzCKiwckuAr2woo_LeTi2GecsnWlPF/view?usp=sharing

[thedocs]

Hi johnyip

I am stuck with same issue, caould u upload a pbix with solution would really help me out?

thx

Connor

[johnyip · Accepted solution]

I figured out a way to achieve my result without forcing a deliberate change to the rank.

I will close this thread now as my problem is solved.

Briefly speaking, I selected those company within the designated rank, and store them into a "list" which will be used as flagging afterwards.

Making a calculated table responding to slicers (both ordinary slicers and those related to the use of measures) are juist challenging yet achievable. It requires A LOT of ad-hoc codes specific to your certain requirements.

Var PathVar =

CONCATENATEX(

VAR A = SUMMARIZE(SourceTable,

                  SourceTable[Company],

                  "Rank",IF([A filter determining whether the rank is in range]=1,

                   RANKX(ALLSELECTED(SourceTable[Company]),SourceTable[This Month Sales],,DESC,Dense),

                   BLANK()))

RETURN ADDCOLUMNS(A,

                  "SELECTED",IF(

                            VAR MinValue = MIN('SourceTable.RankingRange_Company'[Sales ranking range]) // the minimum available range

                            VAR MaxValue = MAX('SourceTable.RankingRange_Company'[Sales ranking range]) // the maximum available range

                            VAR CurrentValue = RANKX(ALLSELECTED(SourceTable[Company]),SourceTable[This Month Sales],,DESC,Dense) // current rank of the record

                            RETURN IF(CurrentValue >= MinValue && CurrentValue <= MaxValue,1,0)

                            =1,SourceTable[Company],BLANK())),[SELECTED],"|")

VAR VarList =

    SELECTCOLUMNS (

        GENERATESERIES ( 1, PATHLENGTH ( PathVar ) ),

        "Item",  PATHITEM ( PathVar, [Value] )

    )

RETURN

IF(MAX('A calculated table which simulates a MATRIX visualization using the SourceTable'[Company]) IN VarList,1,0)

[v-shex-msft]

Hi @johnyip,

It sounds like you add some more category fields to your visual and the expression work failed, right?
AFAIK, the current rankx function does not support auto fit different calculate ranges based on current row context. So you may need to add if statement to check the current row context level and write multiple expressions for these different level calculations.

Clever Hierarchy Handling in DAX - SQLBI

Regards,

Xiaoxin Sheng

[johnyip]

Thanks for the reply.

But what I want to achieve now is rather strange: it is to rank [this month sales] based on company only EVEN WHEN I add anopther row to the matrix.

For example,, using the same picture in my question again, I want to show 2 for all the entries in Company C, because comapny C's [sales rank] is 2 in the first table. Similarly,  Company G and D should have all the entries as 3 and 4 respectively, because their overall sales raning being that number.

Is there anyway I can do that by ising DAX?

I read this article which appears in a thread you have answered. But thtat articleteaches how to rnk based on level, not what I want to achieve now (rank based on parents and fill the results in al the children)

[amitchandak]

@johnyip , As soon as you add one more level, I see one after company, Values will get ranked inside that one

Current one like

Sales rank = RANKX(ALLSELECTED(Sales[Company]),[This Month Sales],,DESC,Dense)

You can add more column

Sales rank = RANKX(summarize(ALLSELECTED(Sales),[Company], Category),[This Month Sales],,DESC,Dense)

We can switch them using isinscope 

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[johnyip]

Cool, and with respect to your link regarding ISINSCOPE(), is there any DAX that I can force the result of RANKX(), calculated based on channel only, to be filled into seller?

To be more clear, is there any DAX in a measure can be used, such that the value of seller equals to that of channel, with channel = RANKX() baswed on all channel?