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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
AliceW
Impactful Individual
Impactful Individual

A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

Hi everyone,

This seems simple, but I can't make it work.

I have one table with the Employee Names - one column, let's call it Employee Name.

I have another table with the Salesforce Opportunities and their Ownes - two columns, Opportunity Owner and Opportunity ID.

The tables are connected via a one-to-many link, based on the Employee Name and Opportunity Owner.

I have one measure: Opportunity Count = DISTINCTCOUNT(Oportunity ID). So far, so good 🙂

However, I need another one: the count of Employee Name with an Opportunity Count of zero (or null?).

I've tried this gem: Employees with Zero Opps = calculate(DISTINCTCOUNT(Employee Name), Opportunity Count).

It crashed.

Could you figure out what I'm doing wrong?

Thank you!

1 ACCEPTED SOLUTION

@AliceW 

Sorry, I misread. What I showed above should yield the number of employees that have opportunities in the Sales Opps table. Try this for those with no opportunities:

Zero Opps V2 =
COUNTROWS (
    EXCEPT (
        DISTINCT ( EmployeeTable[EmployeeName] ),
        DISTINCT ( SalesforceOpportunitiesTable[Opportunity Owner] )
    )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @AliceW 

Try this, where 'SalesforceOpportunitiesTable' is the name of the second table you describe:

Zero Opps =
CALCULATE ( DISTINCTCOUNT ( EmployeeTable[EmployeeName ), SalesforceOpportunitiesTable )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

AliceW
Impactful Individual
Impactful Individual

First of all, thank you for replying so fast!

Second, it gives me the full number of Employees, not the ones without any Opportunities. Could you make this happen, please?

Thanks again!

@AliceW 

Sorry, I misread. What I showed above should yield the number of employees that have opportunities in the Sales Opps table. Try this for those with no opportunities:

Zero Opps V2 =
COUNTROWS (
    EXCEPT (
        DISTINCT ( EmployeeTable[EmployeeName] ),
        DISTINCT ( SalesforceOpportunitiesTable[Opportunity Owner] )
    )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

 

AliceW
Impactful Individual
Impactful Individual

It works!!!

Thank you so much!!!

Helpful resources

Announcements
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.