Forum Discussion
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 |
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!
5 Replies
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:
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.
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!
