Forum Discussion
Return value of Column from Many side table to One side table
Hello!
I have two tables connected One (contacts) to Many (crmd_clientprograms) relationship. In my "contacts" table I have column like "contact_id", and in my "crmd_clientprograms" table I have columns like "contact_value" (this column is related with "contact_id" column of contacts table), "Program Name", "Date Enrolled", "Status Code" etc. I want to create two New Columns in "contacts" table that will return data from "crmd_clientprograms[Program Name]" and "crmd_clientprograms[Date Enrolled]" with Status Code =1 and 2 only corresponding to the "contacts[contact_id]". Can anyone please help me?
Hi ishtiaque ,
According to your statement, I know you want to create two new columns from "crmd_clientprograms[Program Name]" and "crmd_clientprograms[Date Enrolled]" in "contacts" table. The relationship is one to many, so there may be multiple results in "crmd_clientprograms" table. It is not a good to do this by dax, due to we need to expand "contacts" table.
Here I suggest you to try Merge function in Power Query Editor.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- jsaunders_zero9
Responsive Resident
What should happen when there is more than 1 crmd_clientprograms[Program Name] is returned?
Thanks for your reply. Yes, the final result will return more than1 crmd_clientprograms[Program Name]. I am trying to find the correct DAX formula to do this. I hope the attached image will help to better understand the problem (where the Yellow filled shows the One-to-Many relationships and Green filled shows the final result).
Hi ishtiaque ,
According to your statement, I know you want to create two new columns from "crmd_clientprograms[Program Name]" and "crmd_clientprograms[Date Enrolled]" in "contacts" table. The relationship is one to many, so there may be multiple results in "crmd_clientprograms" table. It is not a good to do this by dax, due to we need to expand "contacts" table.
Here I suggest you to try Merge function in Power Query Editor.
Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
