How to group row values for distinct count

[Anonymous]

Data table includes columns for email addresses, name of email sent and column that identifies whether it was opened or not (True\False valuse) I want to create a chart that shows; count of distinct email addresses that did not open any of the email names sent to their email address.

 Example below distinct count would be 0 since email address opened at least 1 email.

Email address	email name	opened
xyz.com	Apples	False
xyz.com	Bannanas	False
xyz.com	Oranges	True

2nd example below, distinct count to be 1 since email address did not open any emails. 

Email address	email name	Opened
abc.com	Apples	False
abc.com	Bannanas	False
abc.com	Oragnes	False

[AlB · Accepted solution]

Hi @Anonymous 

You talk about a chart but mention only one dimension you want to show? Place this measure in a card visual:

Measure =
COUNTROWS (
    FILTER (
        DISTINCT ( Table1[EmailAddress] ),
        CALCULATE ( SUMX ( Table1, Table1[Opened] * 1 ) ) = 0
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[v-yingjl · Accepted solution]

Hi @Anonymous ,

Based on your description, you can create a measure like this:

Distinct =
IF (
    COUNTX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Email address]
                IN DISTINCT ( 'Table'[Email address] )
                    && 'Table'[opened] = TRUE ()
        ),
        [Email address]
    ) >= 1,
    0,
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Email address] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Email address] IN DISTINCT ( 'Table'[Email address] )
        )
    )
)

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thank you for your help. I appreciate you.

[AlB · Accepted solution]

Hi @Anonymous 

You talk about a chart but mention only one dimension you want to show? Place this measure in a card visual:

Measure =
COUNTROWS (
    FILTER (
        DISTINCT ( Table1[EmailAddress] ),
        CALCULATE ( SUMX ( Table1, Table1[Opened] * 1 ) ) = 0
    )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers