Forum Discussion
Helper ICreate Related Column from different table splited by field content
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:
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,
2 Replies
- ibarrau
Super User
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,
arutsjak90Thank you for your help! It is working fine 🙂