Create Dynamic Rank Measure with in group and select top 1 with in group with

[Dhruvarora]

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

[Phil_Seamark]

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

[Phil_Seamark]

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)		

[Dhruvarora]

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

[Phil_Seamark]

Hi @Dhruvarora

I'm not sure I understand your question regarding the two approaches

[Dhruvarora]

Hi @Phil_Seamark

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.

[Phil_Seamark · Accepted 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.