Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @Kcloud ,
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 @Kcloud ,
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |