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 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!
Solved! Go to Solution.
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
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
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!
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
It works!!!
Thank you so much!!!
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |