I recently needed to solve this problem, and thought it was worth sharing as a Power BI DAX “go by” for others.
The situation was creating Power BI dashboards for sales pipeline data coming from a CRM system. In this CRM data model, “Opportunities” have related “Opportunity Product” records that are the “fact” table in this scenario. Opportunity Product has many records related to 1 Opportunity record.
The goal was to only count Opportunities where 1 of many related Opportunity Product “child” records fulfilled a specific condition.
So if Opportunity Product records have, for example, a “RevenueType” column, then only count Opportunities where none of the multiple, related Opportunity Product records had RevenueType = “Existing”.
So I approached creating the needed measures with a layered, building block approach:
- First COUNTROWS() on Opportunity Product.
- Then COUNTROWS() on Opportunity Product where “RevenueType=Existing”.
- Then in a SUMX iterator, using a VALUES() expression using OpportunityID for my table to iterate on, compare each count. If they are the same, return a “1” to SUMX. If they are not the same, return “BLANK()”.
- Once all VALUES() are evaluated in a filter context defined by the unique, related Opportunity ID, SUMX gives you the desired count of Opportunities.
- And you can include this measure in a table view of Opportunities to see which ones have a “1” value, and which do not.
Now there are likely many different ways to do this, and some that are technically “better” than my approach. But it works, and is fairly easy to understand and debug if needed.
DAX Code for Power BI
If you’d like to see the PBIX data model and DAX code, drop me a line at email@example.com and I’ll send it to you.