cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
AliceW Member
Member

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

Accepted Solutions
Super User
Super User

Re: A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

@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
Super User
Super User

Re: A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

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 Member
Member

Re: A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

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!

Super User
Super User

Re: A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

@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

AliceW Member
Member

Re: A DISTINCTCOUNT measure based on another DISTINCTCOUNT measure

It works!!!

Thank you so much!!!

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 91 members 1,558 guests
Please welcome our newest community members: