Forum Discussion
"Matrix multiplication" in DAX
I have fact table and a table with metrics definitions that determins which column is considered for which metrics. The metrics are simply sums of the values of those columns. For example the fact table looks like:
ID Val1 Val2 Val3 A 3 2 9 B 9 4 1 C 4 10 6 D 1 2 7 E 4 6 8
The metrics definition table looks like:
Metric1 Metric2 Val1 1 0 Val2 0 1 Val3 1 1
The result table should be:
ID Val1 Val2 Val3 Metric1 Metric2 A 3 2 9 12 11 B 9 4 1 10 5 C 4 10 6 10 16 D 1 2 7 8 9 E 4 6 8 12 14
e.g. metric1 for A is 3+9 as per the metric definition.
Is there a way to calculate the resulting table in DAX dynamically, i.e. it can cope with changes in the metrics definitions without changing the code itself? Mathematically the calculation is a matrix multiplication.
I would think that Metric1 would be something along the lines of:
Metric1 = SUM(Fact[Val1])*CALCULATE(SUM(Metrics[Metric1]),Column="Val1") + SUM(Fact[Val2])*CALCULATE(SUM(Metrics[Metric1]),Column="Val2") + SUM(Fact[Val3])*CALCULATE(SUM(Metrics[Metric1]),Column="Val3")
Metric2 would be identical other than replace Metric1 with Metric2.
8 Replies
- Greg_Deckler
Community Champion
I would think that Metric1 would be something along the lines of:
Metric1 = SUM(Fact[Val1])*CALCULATE(SUM(Metrics[Metric1]),Column="Val1") + SUM(Fact[Val2])*CALCULATE(SUM(Metrics[Metric1]),Column="Val2") + SUM(Fact[Val3])*CALCULATE(SUM(Metrics[Metric1]),Column="Val3")
Metric2 would be identical other than replace Metric1 with Metric2.
Thx. Greg_Deckler, works great.
One question though, why would you define this as a measure rather than a calculated column?
- Greg_Deckler
In theory you could do it as a column, but I tend to think in measures first. More flexibility. If you did it as a calculated column it may not auto-recalculate if something changes since columns are calculated once. For example. If your Metrics table is something like an Enter Data query and you go in and change something, your calculated column sitting in some other table may not change. Measures will always reflect current state.
- Ashish_Mathur
Super User
Thx. Ashish_Mathur, that works also great.
I just made a small modification. I will keep the original table before the unpivotting and add a relationship to the new table. That way I can save the "Value" measures that just recalculate the original values in my raw data.
- Ashish_Mathur
You are welcome.
I am facing some issue with the same concept. I have two tables, table a and table b. I am trying to do matrix multiplication of table a with row "v" of table B, which itself is considered as second table for matrix multiplication. The next row "v1" would be the generated result. i want to use this generated result "v1" as the next input table and multiply it with table a, and so on and so forth to obtain table b. Please help out as i am unable to find any dax which will help me in getting desired results.
- Greg_Deckler
Anonymous I *think* I did this when I replicated MMULT in Excel: MMULT - Microsoft Power BI Community