Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi All,
Trying to make a measure for KPI tracking where I'm counting each unique creation for a User, i.e.
This seems like it should be simple but it's just not working the way I'd expect.
My three tables are as below:
Table A: 'Conversion'
ConversionId | CreatedById | LeadId | OpportunityId | |||
1 | 10 | 100 | ||||
2 | 10 | 1000 | ||||
3 | 20 | 200 | 2000 | |||
4 | 20 | 200 | 2000 | |||
5 | 10 | 300 | 2000 | |||
6 | 20 |
Table B: 'Lead'
LeadId | CreatedById | OpportunityId | ||
100 | 10 | |||
200 | 20 | 2000 | ||
300 | 10 | 2000 |
Table C: 'Opportunity'
OpportunityId | CreatedById | ConversionId | LeadId | |||
1000 | 20 | 2 | ||||
2000 | 10 | 200 | ||||
2000 | 10 | 300 | ||||
3000 | 10 | |||||
4000 | 20 |
I want to count the unique Id's within each Table to link back for each Staff member (CreatedById) so that the end result for Staff (CreatedById) 10 & 20 would look like this:
Staff Id | Staff Name | Unique Creations | ||
10 | Allison A | 4 | ||
20 | Brendan B | 4 |
Reasoning:
Current Measure:
CALCULATE(
CALCULATE(
DISTINCTCOUNT('Conversion'[ConversionId])
, USERELATIONSHIP('Conversion'[CreatedById], 'User'[Id])
, 'Conversion'[OpportunityId] = BLANK()
, 'Conversion'[LeadId] = BLANK()
)
+
CALCULATE(
DISTINCTCOUNT('Opportunity'[Id])
, USERELATIONSHIP('Opportunity'[CreatedById], 'User'[Id])
)
+
CALCULATE(
DISTINCTCOUNT('Lead'[Id])
, USERELATIONSHIP('Lead'[CreatedById], 'User'[Id])
)
)
This is the closest I've managed, however in my tables above, this would exclude ConversionId 6
Appreciate any assistance with this!
Solved! Go to Solution.
Hi @Anonymous ,
Do you have any other relationships between your tables? How is your model setup?
Believe this is a question about the relationships your model is using, making use of your metric has you can see below with a model with the relationship in the image below everything works properly:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
Do you have any other relationships between your tables? How is your model setup?
Believe this is a question about the relationships your model is using, making use of your metric has you can see below with a model with the relationship in the image below everything works properly:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix ,
I believe you're right. I kept looking at the measure but didn't check the relations.
The model is quite extensive with approximately 30 tables, and after reviewing the four involved it does appear that the cardinality on one of them is incorrect.
Thank you for your help!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
67 | |
57 | |
50 | |
36 | |
34 |
User | Count |
---|---|
84 | |
74 | |
56 | |
45 | |
44 |