cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
adetogni Senior Member
Senior Member

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

I have this schema:

 

2018-10-06_0105.png

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

 

2 REPLIES 2
v-danhe-msft Super Contributor
Super Contributor

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

Hi @adetogni,

Could you please share the pbix file if possible?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
adetogni Senior Member
Senior Member

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

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

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: 142 members 1,690 guests
Please welcome our newest community members: