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

[adoster]

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 counts the Lowest Rank first and then ignores any other procedure done on the same Date.

Table A
Date	Patient Name	Procedure
11/8/2021	Donald Duck	Procedure A
11/8/2021	Donald Duck	Procedure D
11/8/2021	Mickey Mouse	Procedure A
11/8/2021	Mickey Mouse	Procedure B
11/9/2021	Goofy Dog	Procedure F
11/9/2021	Scrooge McDuck	Procedure C
11/9/2021	Scrooge McDuck	Procedure H
11/9/2021	Mickey Mouse	Procedure A
11/10/2021	Mickey Mouse	Procedure G

Table B
Procedure	Rank
Procedure A	1
Procedure B	1
Procedure C	1
Procedure D	2
Procedure E	2
Procedure F	2
Procedure G	3
Procedure H	3

Desired Results
Date	Procedure Display Name	Count by Rank
11/8/2021	Procedure A	2
11/8/2021	Procedure B	0
11/8/2021	Procedure D	0
11/9/2021	Procedure A	1
11/9/2021	Procedure C	1
11/9/2021	Procedure F	1
11/9/2021	Procedure H	0
11/10/2021	Procedure G	1
Total		6

[v-cazheng-msft · Accepted solution]

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]

@adoster  this is the expanded table

How do you arrive to the desired result form here?

[adoster]

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.

[amitchandak]

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

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: