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

The first part:

VAR Refused =
DISTINCT (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Consent] = "Refused" ),
        'Table'[ClientID]
    )
)

defines the variable 'Refused' as the single-column table comprising the distinct values from the ClientID column for which the Consent column entry is "Refused".

The next variable is similarly defined, though for Consent column entries of "Consented".

The EXCEPT clause then returns a single-column table comprising all Client ID entries from the 'Refused' table which do not appear in the 'Consent' table.

Finally, the number of rows in this last table are counted.

Regards

 

JustinDoh1's avatar
JustinDoh1
Post Prodigy
5 years ago

Jos_Woolley Thank you for your explanation. Now I understand what 'Except' does in DAX.

  • Jos_Woolley's avatar
    Jos_Woolley
    Solution Sage
    5 years ago

    You're welcome!

     

    Regards