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.
I have this schema:
This is a SalesForce schema. Each Account (client) has many opportunities (options to buy a product) which may have many Opportunity Product. Then for each Opportunity Product there's a Price Book Entry and a related Product.
What I want to achieve is, for each Account, having how many distinct Family of Products have been sold.
There's probably a more straightforward way to do it, but my approach was two steps
1) add a calculated column in Opportunity Product with this formula
Product Family = related('Product'[Family])
So now I have the Family in the Opportunity Product, at the "closer end" of the one-to-many relationship
2) add a calculated column in Account with this formula
Number of Families = CALCULATE(distinctcount('Opportunity Product'[Product Family]);RELATEDTABLE('Opportunity Product'))
This gets the rows in Opportunity Product which are related to the Account and then calculate the distinct values of Product Family within this subset
Now, this works perfectly, but it's a calculated column and therefore
1) it's memory used
2) it will ignore any filter I apply (for example, choosing a subset of Opportunity)
If I create a Measure with this formula, it doesn't really work, as CALCULATE will ignore any filter applied in the report.
So the questions are 3
1) is the CALCULATE correctly written?
2) was there a more straightforward formula (a single one in the Account instead of 2 ?
3) can i have the 2nd step built as a measure instead of a column
(p.s. I'm a DAX not-anymore-beginner-but-still-definitely-not-even-intermediate :-))
Hi @Anonymous,
Could you please share the pbix file if possible?
Regards,
Daniel He
Hi, unfortunately it's a pbix with lots of business data and I can't share it. Even thinking about cleaning it up, it will take a lot of time...
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 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |