Forum Discussion

tbobolz's avatar
tbobolz
Resolver 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 n...
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" }
        )
    )
)
tbobolz's avatar
tbobolz
Resolver I
7 years ago

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!!!
jmalone's avatar
jmalone
Resolver III
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" }
        )
    )
)
  • jtownsend21's avatar
    jtownsend21
    Responsive Resident
    7 years ago

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

    • jmalone's avatar
      jmalone
      Resolver III
      7 years ago

      jtownsend21  - I have those moments all the time :smileylol:

  • tbobolz's avatar
    tbobolz
    Resolver I
    7 years ago

    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.

     

     

    • tbobolz's avatar
      tbobolz
      Resolver I
      7 years ago

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

       

      Much thanks to both of you!!!

      • jtownsend21's avatar
        jtownsend21
        Responsive Resident
        7 years ago

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