topic How to calculate a measure with values from two fact tables on the lowest level of granularity? in DAX Commands and Tips

How to calculate a measure with values from two fact tables on the lowest level of granularity?

Anonymous — Fri, 21 Aug 2020 13:41:57 GMT

Tabular model has 2 fact tables:

Sales (fact table)

CustomerId

TimePeriodId

ProductId

Amount

and

Discount (fact table)

CustomerId

TimePeriodId

ProductId

Discount

Sales table has [ComplexMeasure] measure that uses another measures chain for calculations, in the lowest level it has SUM(Amount).

I need to add a new measure with formula = ComplexMeasure x Discount, but I can't use here something like average discount, instead of that I shoud multiply every Amount from the Sales table by corresponding Discount from the Discount table. I can't change ComplexMeasure or measures that used in this measure calculation. And I don't want to re-create all these measures, but with SUM(Sales[Amount] x Discount[Discount]) on the lowest level.

Do you have any idea how I could implement that using a DAX expression?

Re: How to calculate a measure with values from two fact tables on the lowest level of granularity?

saraMissBI — Fri, 21 Aug 2020 13:58:35 GMT

Hi Anonymous

You can add a calculated column to table Sales to get the corresponding discount for each customer. Then create the following measure:

NewMeasure = CALCULATE(Sales, SUMX([ComplexMeasure] * [CalculatedColumnDiscount])

This should do the trick.

If my answer solves your problem, please give Kudos and mark as solution 🙂

Thank you!

Re: How to calculate a measure with values from two fact tables on the lowest level of granularity?

amitchandak — Fri, 21 Aug 2020 14:58:26 GMT

Anonymous , Try like

sumx(summarize(Sales, customer[CustomerId],date[TimePeriodId],Product[ProductId], "_1", sum(Sales[Amount]) , "_2", max(Discount[Discount])),[_1]*[_2])

Changes the measure and aggregation as per need

Re: How to calculate a measure with values from two fact tables on the lowest level of granularity?

Anonymous — Sat, 22 Aug 2020 09:56:05 GMT

Hi saraMissBI

It's a very, very, very bad idea to have a measure that would iterate a big fact table and execute CALCULATE against each and every row. The measure will be AGONIZINGLY SLOW and will lead to memory overflow. One should never do it. Apart from that, the measure is syntactically incorrect.

Re: How to calculate a measure with values from two fact tables on the lowest level of granularity?

Anonymous — Mon, 24 Aug 2020 14:27:03 GMT

Yes, it works. Thank you!

It seems that you query groups all data from Sales and then if the coresponding Discount exists adds value to the result.

I would like to increase the performance of the query by filtering Sales items before grouping operation. Is it possible to have grouped sales only that have link to discount table? Probably I should use RELATEDTABLE in this query, but I can't figure out how to use it.