Trying to rank a column within filtered range in a flat file

[Anonymous]

Hi, I have a data file like this:

Zipcode	Label	Value
12222	Label 1	12
12222	Label 2	20
11111	Label 1	5
11111	Label 2	89

What I need is a rank column whichh rankes the labels based on Value WITHIN ALL THE SELECTED (FILETERED ) zipcodes.

Zipcode	Label	Value	Rank
12222	Label 1	12	1
12222	Label 2	20	2
11111	Label 1	5	1`
11111	Label 2	89	2

This below line work properly ONLY FOR ONE ZIPCODE and is obviosuly static:

rank= rankx (FILTER ( ALL('table'), 'table'[zipcode] =("12222")), [value])

I want the same action but for EVERY SELECTED (FILTERED ) zipcode. So I went with this: 

rank= rankx (FILTER ( ALL('table'), ISFILTERED('table'[zipcode]),  [value])

 But it did not work.  It produces the number of selected zipcodes as the rank for every label. 

Thanks 

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can create a measure as below to get the rank changed dynamically base on slicer selections, please find the details in the attachment.

Rank =
RANKX (
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Zipcode] = SELECTEDVALUE ( 'Table'[Zipcode] )
    ),
    CALCULATE ( SUM ( 'Table'[Value] ) ),
    ,
    ASC,
    DENSE
)

If the above one can't help you get the desired result, please provide some sample data in the table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

How to upload PBI in Community

Best Regards

[lbendlin]

Use of RANKX in Power BI measures - SQLBI