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.
Hi,
I have gone through this link. My requirement is little different and i am not sure how to achieve this in power bi. Your help is really appreciated.
To illustrate my requirement-
In table below, i want to create rank (order by TAT column) with in [Customer Name] and rank needs to dynamically updated when i use Date column in filter and filters date values.
Original table with rank to be calculated -->
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep 2
XYZ 2 15 11Sep 1
XYZ 3 25 12Sep 3
ABC 4 10 11Sep 1
ABC 5 15 11Sep 2
ABC 6 20 12Sep 3
ABC 7 23 12Sep 5
ABC 8 22 12Sep 4
If i select filter date on 11Sep, then result should be like this -->
Customer Name Session TAT Date Rank
XYZ 1 20 11Sep 2
XYZ 2 15 11Sep 1
ABC 4 10 11Sep 1
ABC 5 15 11Sep 2
PowerBi Sample OneDrive location - link. It has Rank calculated but it is not partition on [Customer Name].
Solved! Go to Solution.
Oh, I think I undestand.
The 2nd approach checks to see if a selection has been made on your date slicer.
If no seleciton is made, then the customers are ranked on the table regardless of the date. If a selection is made, it ranks on customer/date.
The first approach always ranks on customer/date.
I'm not sure which you prefer, but if you try both, you can keep the one that works best for you.
HI @Dhruvarora
You could try creating a rank as a calculated measure and dragging that to your table visual
Something like
Rank as Measure = CALCULATE( COUNTROWS('ParentData'), FILTER(ALLEXCEPT('ParentData','ParentData'[Date]),[Customer Name ] = MAX([Customer Name ]) && 'ParentData'[Turn Around Time] > MAX('ParentData'[Turn Around Time]) ) )+1
This approach proviced a slightly different result, in that it will calculate the ranking differently depending if a selection has been made on the data slicer. You'll need to remove the date field from your table visual for it to work.
Rank as Measure = VAR x = CALCULATE( COUNTROWS('ParentData') ,FILTER( ALLEXCEPT('ParentData','ParentData'[Date]), 'ParentData'[Customer Name ] = MAX(ParentData[Customer Name ]) && 'ParentData'[Turn Around Time] < MAX('ParentData'[Turn Around Time]) ) )+1 VAR y = CALCULATE( COUNTROWS('ParentData') ,FILTER( ALL('ParentData'), 'ParentData'[Customer Name ] = MAX(ParentData[Customer Name ]) && 'ParentData'[Turn Around Time] < MAX('ParentData'[Turn Around Time]) ) )+1 RETURN IF(ISFILTERED('ParentData'[Date]),x,y)
Hi @Phil_Seamark,
Thanks for the solutions. When i do not filter on dates and do not use date column in visual, i am getting rank as expected from both solutions. Can you please tell me where it could differ? Thanks for your help again.
This is i am getting from both solutions - Rank1(solution 1) and Rank 2 (solution 2)
Customer Name Session TAT Date Rank1 Rank2
XYZ 1 20 11Sep 2 2
XYZ 2 15 11Sep 1 1
XYZ 3 25 12Sep 3 3
ABC 4 10 11Sep 1 1
ABC 5 15 11Sep 2 2
ABC 6 20 12Sep 3 3
ABC 7 23 12Sep 5 5
ABC 8 22 12Sep 4 4
You mentioned that Approach 2 provide a slightly different result. But I am getting same results from both approaches . I was worried that i missed your point you are trying to make between both approaches with slightly different result.
Oh, I think I undestand.
The 2nd approach checks to see if a selection has been made on your date slicer.
If no seleciton is made, then the customers are ranked on the table regardless of the date. If a selection is made, it ranks on customer/date.
The first approach always ranks on customer/date.
I'm not sure which you prefer, but if you try both, you can keep the one that works best for you.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |