Forum Discussion
Helper IIConnecting Two Tables to form one matrix table
Following modeling best practices, you need to change the structure of the model to include Dimension tables for fields common to both tables as follows:
You then use the fields from the Dimension Tables in the visuals, measures, slicers, filters... These dimension tables filter the equivalent rows in both tables.
The slicer is from the Dim Family table; the year field is from the Dim Year table. You will get:
I've attached the sample PBIX file with the changes
Super User
Try to add an Investment column to your first table "family -> product ->colour" by using the lookup DAX code, then use all columns from one table to create your Matrix/Table.
Table 1 = family -> product ->colour
Table 2 = the investment cost with details at the family level
Try this code to add acolumn to the Table 1:
Investment = LOOKUPVALUE('Table 2'[Total Investment],'Table 2'[Investment ID],'Table 1'[Investment ID])
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your Kudos ✌️!!
milkywaypowerbiHi,
Thank you but the solution is not working. Another issue is that the investment table has 2019 while the revenue table only starts from 2020.
The investement family level is for the whole family and should not be assigned to product and colour level.
PaulDBrownCommunity Champion
Following modeling best practices, you need to change the structure of the model to include Dimension tables for fields common to both tables as follows:
You then use the fields from the Dimension Tables in the visuals, measures, slicers, filters... These dimension tables filter the equivalent rows in both tables.
The slicer is from the Dim Family table; the year field is from the Dim Year table. You will get:
I've attached the sample PBIX file with the changes
- milkywaypowerbi
Hi Paul,
Thank you for your response. This is exactly what I am looking for. However, when I tried to do calculated field for profit based in the model. The numbers are not tying up.
I tried
Profit = CALCULATE(SUM('Revenue Data'[Revenue])-SUM(Investment[Total Investment ])-SUM('Revenue Data'[Supplies Cost])-SUM('Revenue Data'[Warranty]))Thank you so much!