Forum Discussion
create rank column based on column using dax
Greetings !!
I have a region slicer which drop downs as Test1, Test2 , Test3. And a Rank slicer which has ranges and occurs based on the Rank column.
i have data set given below .
| Region | ProjectName | Text Value | Rank | Expected RNK |
| Test1 | wewqe | 5.00 | 1 | 1 |
| Test1 | yuy | 5.00 | 1 | 1 |
| Test2 | ty | 20.35 | 2 | 1 |
| Test3 | ert | 48.73 | 3 | 1 |
| Test3 | ertrre | 78.39 | 4 | 2 |
| Test3 | sdada | 78.39 | 4 | 2 |
The above calculation for Rank column is if text value is same, rank is same and increases on next values irrespective of region column .
I am in need of help in creating rank column (Expected RNK) as shown above. ie based on both the text value and region column.
If test 2 is selected in Region slicer and Rank slicer is set to 1, then i need result as given below .
| Region | ProjectName | Text Value | Rank | Expected RNK |
| Test2 | ty | 20.35 | 2 | 1 |
Please help me out.
You can create the column like this:
Column = VAR CurVal = [Text Value] RETURN CALCULATE( RANKX( Data, [Text Value], CurVal, ASC, Dense ), ALLEXCEPT( Data, Data[Region] ) )
4 Replies
You can create the column like this:
Column = VAR CurVal = [Text Value] RETURN CALCULATE( RANKX( Data, [Text Value], CurVal, ASC, Dense ), ALLEXCEPT( Data, Data[Region] ) )LivioLanzo Thanks for your help!! but i am stuck with the calculation
I have created a column as you has specified with ranking order to be desc as given below.
Column = VAR CurVal = [Text Value] RETURN CALCULATE( RANKX( Data, [Text Value], CurVal, Desc, Dense ), ALLEXCEPT( Data, Data[Region] ) )For the dataset given below, calculated column is working as shown .
Region ProjectName Text Value Expected RNK Test1 wewqe 5.00 1 Test1 yuy 5.00 1 Test2 ty 20.35 1 Test2 ty 5.00 2 Test3 wewrt 48.73 3 Test3 frtrre 78.39 2 Test3 sdada 5.00 1 For the Region "Test3" , rank is calculated wrongly as shown above.
Rank should be calculated as
Test3 wewrt 48.73 2 Test3 frtrre 78.39 1 Test3 sdada 5.00 3 Kindly do needful.
Hi Dharini
If you wish the rank to happen the other way around, you just need to change this argument of the RANKX function:
RANKX( Data, [Text Value], CurVal, ASC, Dense )
You - from 4 years ago I guess - saved me a ton of time. I was trying to do a filter and trying to do something with the transform data - I needed to find the top 5 values for each hour and this plus a filter of >=5 on my new column was what I needed - thanks!
