cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

Create Measure for Distinct Count of a column based on criteria from another column

I am trying to create a measure so I can get a unique count of events I am tracking across multiple email campaigns.

 

In my data I have a column for: Email ID and one for Event Type. I often get duplicate email IDs and event types (if someone opens an email multiple times), so I just need to know if that email and event type happened (don't care how many times it happened)

 

I would like to create a measure to return a distinct count of emails IDs when Event Type = X (Delivered/Opened/Clicked).

 

You cannot use a distinct count expression across multiple columns, and I am struggling to find other options.

 

All other things that I've found are creating a separate table, and I just need a measure, not a new table.

 

Please help!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Solution Sage
Solution Sage

Re: Create Measure for Distinct Count of a column based on criteria from another column

@at0kun

 

In your scenario, when Event Type = X you want to return a distinct count of email IDs. Right?

 

Then you can try following DAX expression for a measure:

 

Count Event Type X =
CALCULATE (
    DISTINCTCOUNT ( Table4[Email ID] ),
    FILTER ( ALL ( Table4 ), Table4[Event Type] = "X" )
)

Result shows like: 

 

111.PNG

If above doesn't satisfy your requirement, please kindly share us more information like your source table structure and some sample data with corresponding desired result. So that we can make proper tests.

 

Thanks,
Xi Jin.

View solution in original post

4 REPLIES 4
Highlighted
Regular Visitor

Create Measure for Distinct Count of a column based on criteria from another column

 I am trying to create a measure so I can get a unique count of events I am tracking across multiple email campaigns.

 

In my data I have a column for: Email ID and one for Event Type.  I often get duplicate email IDs and event types (if someone opens an email multiple times), so I just need to know if that email and event type happened (don't care how many times it happened)

 

I would like to create a measure to return a distinct count of emails IDs when Event Type = X (Delivered/Opened/Clicked).

 

You cannot use a distinct count expression across multiple columns, and I am struggling to find other options.

All other things that I've found are creating a separate table, and I just need a measure, not a new table.

 

Please help!

Highlighted
Solution Sage
Solution Sage

Re: Create Measure for Distinct Count of a column based on criteria from another column

@at0kun

 

In your scenario, when Event Type = X you want to return a distinct count of email IDs. Right?

 

Then you can try following DAX expression for a measure:

 

Count Event Type X =
CALCULATE (
    DISTINCTCOUNT ( Table4[Email ID] ),
    FILTER ( ALL ( Table4 ), Table4[Event Type] = "X" )
)

Result shows like: 

 

111.PNG

If above doesn't satisfy your requirement, please kindly share us more information like your source table structure and some sample data with corresponding desired result. So that we can make proper tests.

 

Thanks,
Xi Jin.

View solution in original post

Highlighted
Regular Visitor

Re: Create Measure for Distinct Count of a column based on criteria from another column

Thanks Xi Jin, that did the trick!  

Highlighted
Frequent Visitor

Re: Create Measure for Distinct Count of a column based on criteria from another column

If I needed to add and additional Column criteria what would that look like?

 

Helpful resources

Announcements
Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Upcoming Events

Community Summit North America – Join Online!

Join this community-driven Power Platform digital event for unbiased support and problem-solving.

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Top Solution Authors