Forum Discussion
Dynamic table based on values from 2 different tables
Hi
This is my Table structure.
| Table 1 | Table 2 | ||||
| No | Item | No | Subcat | DES | |
| 1 | A | 1 | CAT1 | AAA | |
| 2 | B | 2 | CAT2 | BBB | |
| 3 | C | 3 | CAT3 | CCC | |
| 4 | D | 4 | CAT4 | DDD | |
| 5 | E | 5 | CAT5 | EEE |
I have to Create Dynamic table as follows based on the values from above 2 tables. can you please assist , how do I create in BI.,
| No | ITEM | SUBCAT | Des |
| 1 | A | CAT1 | AAA |
| 2 | A | CAT2 | BBB |
| 3 | A | CAT3 | CCC |
| 4 | A | CAT4 | DDD |
| 5 | A | CAT5 | EEE |
| 6 | B | CAT1 | AAA |
| 7 | B | CAT2 | BBB |
| 8 | B | CAT3 | CCC |
| 9 | B | CAT4 | DDD |
| 10 | B | CAT5 | EEE |
| 11 | c | CAT1 | AAA |
| 12 | c | CAT2 | BBB |
| 13 | c | CAT3 | CCC |
| 14 | c | CAT4 | DDD |
| 15 | c | CAT5 | EEE |
lm_suresh For this, you can first create a calculated table in Power BI by loading both Table 1 and Table 2. Then, use the DAX function GENERATE to create all possible combinations between the two tables. Next, you can add any additional columns like a sequential number or other needed columns using DAX functions like ADDCOLUMNS. Finally, use this new calculated table in your visuals to display the dynamic relationships between the values.
5 Replies
lm_suresh For this, you can first create a calculated table in Power BI by loading both Table 1 and Table 2. Then, use the DAX function GENERATE to create all possible combinations between the two tables. Next, you can add any additional columns like a sequential number or other needed columns using DAX functions like ADDCOLUMNS. Finally, use this new calculated table in your visuals to display the dynamic relationships between the values.
Hi lm_suresh,
Thank you for reaching out in Microsoft Community Forum.
Thank you ryan_mayu , Akash_Varuna for the helpful response.
As suggested by ryan_mayu, Akash Varuna., I hope this information was helpful. Please let me know if you have any further questions or you'd like to discuss this further. If this answers your question, please "Accept as Solution" and give it a 'Kudos' so others can find it easily.
Please continue using Microsoft community forum.
Regards,
Pavan.Hi lm_suresh,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please "Accept as Solution" and give a 'Kudos' so other members can easily find it.
Thank you,
Pavan.Hi lm_suresh,
I wanted to follow up since we haven't heard back from you regarding our last response. We hope your issue has been resolved.
If the community member's answer your query, please mark it as "Accept as Solution" and select "Yes" if it was helpful.
If you need any further assistance, feel free to reach out.
Thank you,
Pavan.
