Forum Discussion

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago
Solved

Exclude distinct count with OR

I am trying to write DAX formula with following logic:   Count distinct "ClientID" if one has "Refused" in Consent column, BUT, if one has "Consented" in Consent column, do not count (exclude) dis...
Help with DAX
  • Jos_Woolley's avatar
    Jos_Woolley
    5 years ago

    Ok, thanks for clarifying.

    Refused Not Consented =
VAR Refused =
    DISTINCT (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[Consent] = "Refused" ),
            'Table'[ClientID]
        )
    )
VAR Consented =
    DISTINCT (
        SUMMARIZE (
            FILTER ( 'Table', 'Table'[Consent] = "Consented" ),
            'Table'[ClientID]
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( Refused, Consented ) )

    Regards

Jos_Woolley's avatar
Jos_Woolley
Solution Sage
5 years ago

Hi,

In the example you give, the only two possible entries in the 'Consent' column are 'Consented' and 'Refused'. As such, you can use:

Refused =
DISTINCTCOUNT ( 'Table'[ClientID] )
    - CALCULATE (
        DISTINCTCOUNT ( 'Table'[ClientID] ),
        'Table'[Consent] = "Consented"
    )

If other entries are in fact possible within the 'Consent' column then please update your post with a more realistic dataset.

Regards

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago

Jos_Woolley Sorry. actually, there are different options for the value in the  "Consent" column (like 'Historial', 'Not Eligible').