Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
LeighBarber
New Member

Measure to show Max against a distinct category

Relatively new to using Power BI and am trying to create a measure to do the following

 

Working on the below information, return the sum of opens of an email received and clicks within said email from unique users related to the differing dates they did this:

 

Date

Forename

Surname

Job Title

Company

Email

Delivered

Error

Opened

Clicks

Role Name

06/09/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 1

06/09/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 2

06/09/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 3

16/11/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

1

Role 1

16/11/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

1

Role 2

16/11/2022

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

1

Role 3

17/01/2023

Joe

Bloggs

Team Member

Organisation 2

Email2@email.com

1

0

1

2

Role 1

17/01/2023

Joe

Bloggs

Team Member

Organisation 2

Email2@email.com

1

0

1

2

Role 2

17/01/2023

Joe

Bloggs

Team Member

Organisation 2

Email2@email.com

1

0

1

2

Role 3

17/01/2023

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 1

17/01/2023

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 2

17/01/2023

Joe

Bloggs

Team Member

Organisation 1

Email@email.com

1

0

1

2

Role 3

 

The issue with the data is that each user appears more than once in the originating table, as you can see. This is due to the origin of the data noting each person related to the role they have been given in our system. Where they have multiple roles, they are pulled through multiple times.

 

The measure I have in place to calculate opens is as follows and works fine, giving me the numbers I would expect:

 

DistinctOpenedCount =

CALCULATE(

          DISTINCTCOUNT('Table'[Email]),

          'Table'[Opened] = { 1 }

)

 

This shows what I would expect to see:

 

Date

Opened

06/09/2022

1

16/11/2022

1

17/01/2023

2

 

 

However the measure in place to calculate clicks is as follows and I would assume would work the same, but doesn’t:

 

DistinctClickedCount =

CALCULATE(

          DISTINCTCOUNT('Table'[Email]),

          'Table'[Clicks] = { 1 }

)

 

This appears to be adding all the figures up so giving me the following

 

Date

Clicks

06/09/2022

6

16/11/2022

3

17/01/2023

12

 

 

 instead of:

 

 

Clicks

06/09/2022

2

16/11/2022

1

17/01/2023

4

 

 

Any ideas where this might be wrong or a way to create a measure to assist would be very helpful.

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

Hi @LeighBarber ,

 

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create a measure. 

DistinctClickedCount = 
DISTINCTCOUNT('Table'[Email]) * VALUES('Table'[Clicks])

(3) Then the result is as follows.

vtangjiemsft_0-1691719678056.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

View solution in original post

1 REPLY 1
v-tangjie-msft
Community Support
Community Support

Hi @LeighBarber ,

 

According to your description, here are my steps you can follow as a solution.

(1)My test data is the same as yours.

(2) We can create a measure. 

DistinctClickedCount = 
DISTINCTCOUNT('Table'[Email]) * VALUES('Table'[Clicks])

(3) Then the result is as follows.

vtangjiemsft_0-1691719678056.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.