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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tbobolz
Resolver I
Resolver I

How to calculate unique count based on multiple column values

Hi All, I'm struggling to figure out a DAX formula to count the "unique" account numbers in the below example if a select set of DRG and/or ICD10 values match in the related columns of each account number row.

 

I'm not sure how to write the "or" statement so that it counts the "unique" Account Numbers only once, even if both both the DRG and ICD10 column meet the condition, or just one of these columns meet the condition. In the below example, the count should be 5.

 

DRG to match = 10 , 266, 621

ICD10's to match = S48.23, C24, P12

 

Thanks for any help you can offer

 

Terry

 

Account NumberDRGICD10
20010R045
201215J156
202266J450
202266J450
203248S48.23
204621C24
204621C24
204621C24
205423G56
206456W12
207542P12
208110H54.321
1 ACCEPTED SOLUTION

You might try adding a FILTER function to your measure. The concept is the same, but the syntax should allow multiple columns in the condition.

 

Test 2 =
CALCULATE (
    DISTINCTCOUNT ( sj_strata_encounter_hb[FINNBR] ),
    FILTER (
        sj_strata_encounter_hb,
        OR (
            sj_strata_encounter_hb[MS DRG CODE] IN { "10", "266", "621" },
            sj_strata_encounter_hb[ADMIT ICD10 DX CODE] IN { "S48.23", "C24", "P12" }
        )
    )
)

View solution in original post

11 REPLIES 11
jtownsend21
Responsive Resident
Responsive Resident

Trying to understand. You want the Count of Unique Account numbers where the DRG is duplicated or the ICD10 is duplicated? 

Thanks for the reply,

 

I just want to count the unique accont numbers if that row's (DRG or ICD columns) has one or more of the listed conditions met.

 

If DRG (10 or 266 or 621) or ICD10 (S48.23 or C24 or P12) is listed anywhere on the row, in the DRG column or the ICD10 column or both columns, it would count as 1. However if the account numbe ris repeated, it is still only counted as 1 

 

So Account Number 204 has DRG (621) and ICD10 (C24); however, acount number 204 is repeated 3 times, but the total count would only be 1

 

And account number 203 only has ICD10 match of S48.23, so it would be counted as one.

 

I hope I have explained that better.

 

Thanks

 

 

Terry

 

Understood. If you are trying to use slicers, it is a tricky issue. Let me know if you are trying to use Slicers (or filters). 

 

If you aren't using slicers, then I assume you want to hard code the values. You could use something like the following: 

 

Account Number Distinct Count = 
CALCULATE(
    DISTINCTCOUNT([Account Number]),
    OR(
        [DRG] IN { "10", "266", "621" },
        [ICD10] IN { "S48.23", "C24", "P12" }
    )
)

 

I'm hard coding a measure.

 

I get an error that" The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression"

 

Here is my test measure:

 

Test 2 = CALCULATE(DISTINCTCOUNT(sj_strata_encounter_hb[FINNBR]), OR(sj_strata_encounter_hb[MS DRG CODE] IN { "10", "266", "621" }, sj_strata_encounter_hb[ADMIT ICD10 DX CODE] IN { "S48.23", "C24", "P12"}))
 
Thanks again for your help!!!

You might try adding a FILTER function to your measure. The concept is the same, but the syntax should allow multiple columns in the condition.

 

Test 2 =
CALCULATE (
    DISTINCTCOUNT ( sj_strata_encounter_hb[FINNBR] ),
    FILTER (
        sj_strata_encounter_hb,
        OR (
            sj_strata_encounter_hb[MS DRG CODE] IN { "10", "266", "621" },
            sj_strata_encounter_hb[ADMIT ICD10 DX CODE] IN { "S48.23", "C24", "P12" }
        )
    )
)

Thanks to both of you for your help. I still learning my world in more advanced DAX.

 

jmalone, the measure was excepted, however, it states it can not load the visual, "MdxScript(Mdoel) (20, 180) Calculation error in measure, ....Test2: Function 'CONTAINSROW' does not support comparing values of Textwith values of type Integar. Consider using the VALUE or FORMAT finction to convert one of the values"

 

I sure I just need to walk away from this and I might be able to see my issue. LOL

 

Any thoughts.

 

 

Yep, went for a walk and it sovled my issue.

 

Much thanks to both of you!!!

Was the issue the quotations on the DRG values in the function? 

Yes sir, DRG is in whole number format, while ICD10 is text! A simple mistake, even for me!

 

Thanks you two, my kids might see me tonight after all.

@jmalone I just had an "Oh Duhh" moment. Your way should be easier/better. 

@jtownsend21  - I have those moments all the time Smiley LOL

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.