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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors