Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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] ) )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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!
@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 )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |