Forum Discussion

adoster's avatar
adoster
Resolver I
4 years ago

Dax Help: Performing a Count by Ranking system

Hello,


I am trying to do a count of procedures based on a ranking system. So that it only counts the 1st rank and not subsequent ranks on any given day.

If a Person has multiple procedures on the same day, it should only return a count of the lowest rank (between 1-3)

 

Example and desired results:

DateNameProcedure NameRank
11/8/2021Donald DuckProcedure A1
11/8/2021Donald DuckProcedure B2
11/8/2021Mickey MouseProcedure A1
11/8/2021Mickey MouseProcedure B2
11/9/2021Donald DuckProcedure A1
11/9/2021Donald DuckProcedure C3
11/9/2021Mickey MouseProcedure B1
11/10/2021Mickey MouseProcedure C3
    
  Desired Results 
DateProcedure Display NameCount by Rank 
11/8/2021Procedure A2 
11/8/2021Procedure B0 
11/8/2021Procedure C0 
11/9/2021Procedure A1 
11/9/2021Procedure B1 
11/9/2021Procedure C0 
11/10/2021Procedure A0 
11/10/2021Procedure B0 
11/10/2021Procedure C1 
Total Count 5 

 

I need to be able to graph the individual procedure counts based off this logic. Not just the total.

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    adoster , Create a column

     


    Rank = rankx(filter(Table, [Date] =earlier([Date]) && [Name] = earlier([Name]) ), [Procedure Name],,asc, dense)

     

    and then a measure 

    Count Rank = calculate(count(Table[Rank]), allexcept(Table, Table[Name], Table[Date]), filter(Table Table[Rank]=1))

    • adoster's avatar
      adoster
      Resolver I
      4 years ago
      Tried your suggestion.
      DAX didn't recognize the ",,asc, dense" portion. I tried just leaving it out. 
       
      Here is what I tried using my table/column names.
      Rank = RANKX(FILTER('All Data','All Data'[SERVICE DATE] = earlier('All Data'[SERVICE DATE] && 'All Data'[PATIENT NAME], [PROCEDURE_DESC])))
       
      I'm unsure what values we are trying to generate on this column.
      This returns an #ERROR down the column.
       
      I should note that my Rankings column is on a separate Excel file that I've imported in linked by the [PROCEDURE_DESC]