cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Resolver I

## 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 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
1 ACCEPTED SOLUTION
Community Support

You may try this solution.

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!

5 REPLIES 5
Community Support

You may try this solution.

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!

Super User

@adoster  this is the expanded table

How do you arrive to the desired result form here?

Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Resolver I

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.

Super User

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

Resolver I

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:

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors