Using a measure instead of a calculated column...uhm...

[Anonymous]

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 :-))

[v-danhe-msft]

Hi @Anonymous,

Could you please share the pbix file if possible?

Regards,

Daniel He

[Anonymous]

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