Forum Discussion

adoster's avatar
adoster
Resolver I
4 years ago
Solved

Dax help: Distinct Count by Date, Name, and Ranking system

I have 2 tables. One table with Patients, Procedures, and Dates One table with Procedures with associated "Ranks"   I am trying to count the number of Procedures by Date per patient where it only...
  • v-cazheng-msft's avatar
    v-cazheng-msft
    4 years ago

    Hi adoster 

     

    You may try this solution.

    1 Add an Index column to your TableA

     

    2 Create a Calculated column in TableA

    RankProcedure =

    RANKX (

        FILTER (

            TableA,

            TableA[Date] = EARLIER ( TableA[Date] )

                && TableA[Patient Name] = EARLIER ( TableA[Patient Name] )

        ),

        RELATED ( TableB[Rank] ) * TableA[Index],

        ,

        ASC,

        DENSE

    )

     

    3 Create a Measure

    NameCountbyRank =

    VAR res =

        CALCULATE (

            COUNT ( TableA[Procedure] ),

            FILTER ( TableA, TableA[RankProcedure] = 1 ),

            ALLEXCEPT ( TableA, TableA[Date] )

        )

    RETURN

        IF ( ISBLANK ( res ), 0, res )

     

    Then, the result should look like this:

     

    For more details, please refer the attached pbix file.

     

    Best Regards,

    Community Support Team _ Caiyun

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. If you still have problems on it or I misunderstand your needs, please feel free to let us know. Thanks a lot!

     

smpa01's avatar
smpa01
Community Champion
4 years ago

adoster  this is the expanded table

 

 

How do you arrive to the desired result form here?

adoster's avatar
adoster
Resolver I
4 years ago

On 11/8/21 - Procedure A is counted once for Donald Duck & once for Mickey Mouse (total = 2). Procedures B & D are ignored as each patient has already been counted once for that day.

 

On 11/9/21 - Procedure F is counted once for Goofy Dog, Procedure C is counted for Scrooge McDuck, Procedure A is counted for Mickey Mouse (total = 3). 

Procedure H is ignored because that patient has already been counted for that day.