Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

How to group row values for distinct count

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 addressemail nameopened
xyz.comApplesFalse
xyz.comBannanasFalse
xyz.comOrangesTrue

 

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

 

Email addressemail nameOpened
abc.comApplesFalse
abc.comBannanasFalse
abc.comOragnesFalse

 

 

 

Need Help
  • AlB's avatar
    AlB
    5 years ago

    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's avatar
    v-yingjl
    5 years ago

    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.

3 Replies

  • AlB's avatar
    AlB
    Community Champion
    5 years ago

    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's avatar
    v-yingjl
    Community Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you for your help. I appreciate you.