Forum Discussion

tbobolz's avatar
tbobolz
Icon for Resolver I rankResolver I
7 years ago
Solved

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
Need Help
  • jmalone's avatar
    jmalone
    7 years ago

    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" }
        )
    )
)

11 Replies

  • jtownsend21's avatar
    jtownsend21
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

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

    • tbobolz's avatar
      tbobolz
      Icon for Resolver I rankResolver I
      7 years ago

      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

       

      • jtownsend21's avatar
        jtownsend21
        Icon for Responsive Resident rankResponsive Resident
        7 years ago

        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" }
    )
)