create rank column based on column using dax

[Dharini]

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.

[LivioLanzo · Accepted solution]

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] )
)

[Falconias]

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!

[Dharini]

@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.

[LivioLanzo]

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 )