cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JustinDoh1
Continued Contributor
Continued Contributor

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

 

JustinDoh1_0-1628790408502.png

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. 

 

 

1 ACCEPTED SOLUTION
Jos_Woolley
Solution Sage
Solution Sage

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

View solution in original post

7 REPLIES 7
Jos_Woolley
Solution Sage
Solution Sage

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  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.

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

 

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

You're welcome!

 

Regards

Jos_Woolley
Solution Sage
Solution Sage

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

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors