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!

     

amitchandak's avatar
amitchandak
Super User
4 years ago

adoster , Assume both tables are joined

 

Try a measure like

countrows(Filter(Table2,Table2[Rank] = Minx(filter(Allselected(Table1), Table[Date] = max(Table[Date])), calculate(min(Table2[Rank]))) ))

  • adoster's avatar
    adoster
    Resolver I
    4 years ago

    I created a simple PowerBI file and tested your measure as follows: 

    TestCount = COUNTROWS(Filter(TableB, TableB[Rank] = MINX(Filter(ALLSELECTED('TableA'), 'TableA'[DATE] = MAX('TableA'[DATE])),
    Calculate(min(TableB[Rank])))))
     
     
    Here are the results it returns: