Forum Discussion

Dhruvarora's avatar
Dhruvarora
Regular Visitor
8 years ago
Solved

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

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

 

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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.

6 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      8 years ago

      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's avatar
        Dhruvarora
        Regular Visitor
        8 years ago

        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