topic Re: Dax help: Distinct Count by Date, Name, and Ranking system in DAX Commands and Tips

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

adoster — Mon, 13 Dec 2021 16:19:41 GMT

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

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

amitchandak — Mon, 13 Dec 2021 16:55:20 GMT

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

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

smpa01 — Mon, 13 Dec 2021 17:00:16 GMT

adoster this is the expanded table

How do you arrive to the desired result form here?

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

adoster — Mon, 13 Dec 2021 17:22:18 GMT

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.

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

adoster — Mon, 13 Dec 2021 17:47:17 GMT

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:

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

v-cazheng-msft — Fri, 17 Dec 2021 05:46:46 GMT

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!