Applying filter to measure but keep the rank as unfiltered. Is it possible?

[skytulip33]

I am wondering if it is possible to apply filter to the measure but still keep the rank as if no filter is applied?

For example:

item	value	rank
a	100	1
b	200	2
c	300	3
d	400	4

the items are ranked using dax based on their value in ascending order.  What I want to do is to apply further filter and display only item "c" but still keep its rank. so the result should be:

item	value	rank
c	300	3

Is this possible? 

Thank you guys.

[Maddy]

For multiple columns not working

My table like this

RsM Name' Psm Name 'ach% (measure)

[Maddy]

For multiple columns not working

My table like this

RsM Name' Psm Name 'ach% (measure)

I want 2 ranks 1 dynamic and 1 overall rank static

[v-cherch-msft · Accepted solution]

Hi @skytulip33

 Below is the rank measure and rank column for your reference. You may test and check if they could work. If it is not your case, could you explain which filter will be used? Please share more details for your scenario so that we could help further on it.

Rank = RANKX(ALL(Table1[item]),CALCULATE(SUM(Table1[value])),,ASC)

Column = RANKX(ALL(Table1),CALCULATE(SUM(Table1[value]),ALLEXCEPT(Table1,Table1[item])),,ASC,Dense)

Regards,

Cherie

[Asaezmata]

hi @v-cherch-msft  thanks a lot for the solution. I took it for a problem I had an it somewhat solved it... the problem I am seeing now is that whenever I filter for the Rank 1 or Rank 2 (that is, the item that has rank 1 and 2 respectively), the rank automatically turns to 2 and 3. the rest of Ranks work well. i only have the issue with Rank 1 and 2 (or in this case negative 1 and 2 since they have negative profit)

do you know what could be causing it?

Here´s the Measure for the Rank:

var _result =

-1* RANKX (

     FILTER (

            ALL ( Unique_UI[Item], Unique_UI[Region])

            NOT ( ISBLANK ( [Profit]) )

             ),

     [Profit] , , 1

)

return

IF(HASONEVALUE(Unique_UI[Item]),_result, "")

Also, the Table Unique_UI includes columns for Region, country, and Item

Any help would be appreciated!

Alejandro

[skytulip33]

@v-cherch-msft

Thank you Cherie,

That is what I was looking for. I appreciate it!

[JulietZhu]

Sort vaule based on rank column. Is this you are looking for? If yes, go to data view --------> Modeling -----------> find value column ---------> sort by column (from here you can sort by any column you want).