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

[Anonymous]

Tabular model has 2 fact tables:

Sales (fact table)

CustomerId

TimePeriodId

ProductId

<Several another dimensions Ids>

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?

[amitchandak · Accepted solution]

@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

[Anonymous]

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. 

[saraMissBI]

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!

[Anonymous]

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.