Create Related Column from different table splited by field content

[arutsjak90]

Good morning,
I would like to know if it is possible to create several columns from one, calling them by. data in another column using function Related.

Example below: 

Existing Tab1:

Snapshot	Material	Forecast
1	X	20
1	Y	30
2	X	40
2	Y	60
3	X	60
3	Y	90

Existing tab2:

Material	Forecast	Sales	Month
Y	180	200	01
X	120	30	01

What I need:

Material	Sales	Forecast1	Forecast2	Forecast3	Month
X	200	20	40	60	01
Y	30	30	60	90	01

Forecast column number should be based on Snapshot value from Tab1.

My current simple DAX: 

ForecastKG = RELATED(ForecastCummulative[ForecastKG])

[ibarrau · Accepted solution]

Hi. You can do this, but it wont' create the columns dynamically.  You have to create each column in table 2. When you try to get values from a (*) Table to the (1) Table of the relationship, you have to consider that there are more than one row of tab 1 for each row in tab 2. That is why you must aggregate the values.

Assuming the tables have a relationship with "Material" column, you can try like this, adding new column in tab 2:

Forecast 1 =
SUMX( 
    FILTER(
        RELATEDTABLE(Tab1),
        Tab1[Snapshot] = 1
    ),
    Tab1[Forecast]
)
    

It could also be resolved with calculate if you understand context transition.

Hope this helps,
Regards,

[arutsjak90]

Thank you for your help! It is working fine 🙂