Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 | Delivered | Error | Opened | Clicks | Role Name | |
06/09/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 2 | Role 1 | |
06/09/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 2 | Role 2 | |
06/09/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 2 | Role 3 | |
16/11/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 1 | Role 1 | |
16/11/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 1 | Role 2 | |
16/11/2022 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 1 | Role 3 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 2 | 1 | 0 | 1 | 2 | Role 1 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 2 | 1 | 0 | 1 | 2 | Role 2 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 2 | 1 | 0 | 1 | 2 | Role 3 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 2 | Role 1 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 1 | 1 | 0 | 1 | 2 | Role 2 | |
17/01/2023 | Joe | Bloggs | Team Member | Organisation 1 | 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.
Solved! Go to Solution.
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.
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.
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.
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.
User | Count |
---|---|
77 | |
77 | |
68 | |
67 | |
49 |
User | Count |
---|---|
108 | |
104 | |
93 | |
83 | |
64 |