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

Join 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.

Reply
Anonymous
Not applicable

Measure to sum the count of Distinct Id's from separate tables, with conditional exclusion

Hi All,

 

Trying to make a measure for KPI tracking where I'm counting each unique creation for a User, i.e.

  • If the User created the Opportunity/Lead and then the resulting Conversion, I only want to count that as 1.
  • If the User created the Conversion from someone elses Opportunity/Lead, I want it to count as 1 for both Users.

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: 

  • Allison A (Id 10) Created 3 Opportunities, 2 Leads and 3 Conversions.
    • Of the 3 Conversions, One was from their Created Lead, One was from their Created Opportunity
    • Of the 2 Leads, One was from their Created Opportunity
  • Therefore their Unique Creations are made up from:
    • OpportunityId's
      • 2000
      • 3000
    • LeadId
      • 100
    • ConversionId
      • 2

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!

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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:

MFelix_0-1664317844905.pngMFelix_1-1664317940356.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

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:

MFelix_0-1664317844905.pngMFelix_1-1664317940356.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

Hi @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! 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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