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.
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?
Solved! Go to 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 , 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
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.
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!
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 |
---|---|
38 | |
26 | |
20 | |
15 | |
8 |
User | Count |
---|---|
69 | |
47 | |
46 | |
20 | |
16 |