topic Re: Exclude distinct count with OR in DAX Commands and Tips

Exclude distinct count with OR

JustinDoh1 — Thu, 12 Aug 2021 18:45:45 GMT

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) distinct "ClientID".

So, client 69 and 12468 should be excluded from counting.

This is my original DAX:

Refused = CALCULATE (

DISTINCTCOUNT( Table[ClientID] ),

Table[Consent] = "Refused"

)

---------------------------------------------------------------------------

Do I just subtract like this?

Refused = CALCULATE (

( DISTINCTCOUNT(Table[ClientID] ),

(

( Table[Consent] = "Refused" )

(Table[Consent] = "Refused" && Table[Consent] = "Consented" )

)

I don't think it works.

I need to find the way to calculate the count ClientID level, not table level.

Re: Exclude distinct count with OR

Jos_Woolley — Thu, 12 Aug 2021 18:46:27 GMT

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

Re: Exclude distinct count with OR

JustinDoh1 — Thu, 12 Aug 2021 18:50:32 GMT

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

Re: Exclude distinct count with OR

Jos_Woolley — Thu, 12 Aug 2021 19:46:49 GMT

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

Re: Exclude distinct count with OR

JustinDoh1 — Thu, 12 Aug 2021 20:52:22 GMT

Jos_Woolley Thank you for your help. I am trying to understand the logic here. First, we are counting all rows that have a word "Refused". Then, we are also counting all rows that have a word "Consented". Is it right? Then, what does the rest of statement mean? I think I understand what 'Except' means, but from what dataset (from all criteria - including others ('Historial', 'Not Eligible', etc.)? Or am I totally off the track? Thank you.

Re: Exclude distinct count with OR

Jos_Woolley — Thu, 12 Aug 2021 21:08:15 GMT

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

Re: Exclude distinct count with OR

JustinDoh1 — Thu, 12 Aug 2021 21:16:08 GMT

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

Re: Exclude distinct count with OR

Jos_Woolley — Thu, 12 Aug 2021 21:18:06 GMT

You're welcome!

Regards