PowerQuery/count function

[Anonymous]

Hello,

I was trying to create a measure or a column which provides me the count of Case ID's which has both the replacemnet andtransplant category in procedure. The data is as follows.

Procedure	Case ID
replacement	2
replacemnet	3
Transplant	3
Transplant	2
Transplant	4
Transplant	4
replacemnet	5
Transplant	5
Transplant	6
Transplant	7

answer:
Count of case Id that have bth replacemnet and transplant are 3.

[parry2k · Accepted solution]

@Anonymous this measure will do it, change table and column name as per your model

Count Case = 
COUNTX ( 
    SUMMARIZE ( 
        FILTER ( 'Case', 'Case'[Procedure] IN { "Replacement", "Transplant" } ), 
        'Case'[Case ID], 
        "@count", DISTINCTCOUNT ( 'Case'[Procedure] ) 
    ), 
    IF ( [@count] = 2, 1 ) 
)

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡