Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
adoster
Resolver I
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
DatePatient NameProcedure
11/8/2021Donald DuckProcedure A
11/8/2021Donald DuckProcedure D
11/8/2021Mickey MouseProcedure A
11/8/2021Mickey MouseProcedure B
11/9/2021Goofy DogProcedure F
11/9/2021Scrooge McDuckProcedure C
11/9/2021Scrooge McDuckProcedure H
11/9/2021Mickey MouseProcedure A
11/10/2021Mickey MouseProcedure G

 

 

Table B
ProcedureRank
Procedure A1
Procedure B1
Procedure C1
Procedure D2
Procedure E2
Procedure F2
Procedure G3
Procedure H3

 

 

Desired Results
DateProcedure Display NameCount by Rank
11/8/2021Procedure A2
11/8/2021Procedure B0
11/8/2021Procedure D0
11/9/2021Procedure A1
11/9/2021Procedure C1
11/9/2021Procedure F1
11/9/2021Procedure H0
11/10/2021Procedure G1
   
Total 6
1 ACCEPTED SOLUTION
v-cazheng-msft
Community Support
Community Support

Hi @adoster 

 

You may try this solution.

1 Add an Index column to your TableA

vcazhengmsft_0-1639719875472.png

 

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:

vcazhengmsft_1-1639719875477.png

 

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!

 

View solution in original post

5 REPLIES 5
v-cazheng-msft
Community Support
Community Support

Hi @adoster 

 

You may try this solution.

1 Add an Index column to your TableA

vcazhengmsft_0-1639719875472.png

 

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:

vcazhengmsft_1-1639719875477.png

 

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
Super User
Super User

@adoster  this is the expanded table

 

smpa01_0-1639414790666.png

 

How do you arrive to the desired result form here?

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

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:
adoster_0-1639417612913.png

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.