Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
NLNewbie
Frequent Visitor

Copy a column from a secondary related table with DAX

Hi!

 

I have three tables. Table1 is related to table2 (one to many) and table2 to table3 (also one to many).

There is a column in table 3 and I would like to copy certain values of that column to table 1. These values can be selected by applying a filter.

 

It can be done by copying table3, filtering this table to retrieve the values, then copying table2 and merging this one with table3, and finally merging table1 with table2. However, I would like to solve this by using a DAX query, since I would like to use it for multiple columns of various secondary related tables.

 

I hope there is someone who can help me with this.

Thanks in advance!

1 ACCEPTED SOLUTION

right ok this is something i also been struggling with lately the best way to do this, i am not sure i have all the answers.

it worked on mine but i think i have a different set of data to you.

 

options are;

1. do the merge process via power query  (as you said merge table 1 and 2 and then duplicate 3 with the filtered table to that result) - you did this already i think?

2. do a summarize in dax 

 

 

personally i would go for 1

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




View solution in original post

13 REPLIES 13
vanessafvg
Super User
Super User

you could use related

 

i.e in the table you want to create the column

column = related (othertable[fieldname])





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




vanessafvg... Your solution is exactly what I've been searching for.  Thank you!

Thanks for your reply!

 

For table1 I tried:

    column = RELATED(table2)

and

    column = RELATED(table2[desired column])

 

Both don't work.

With the first one I get the error: "Function RELATED expects a fully qualified column reference as its argument."

With the second one I get the error: "The column 'table2[desired column]' either doesn't exist or doesn't have a relationship to any table available in the current context."

 

I checked the spelling and the relationships, there is a direct relationship between table1 and table2 (one to many).

 

So now I don't know why I get these errors. Possibly due to the nature of the relationship between table1 and table2 (one to many)?

it would be easier to visualise what you saying if you could just post a screenshot of your tables and relationships with the column you want to copy to another table.





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




table1table1table2table2table3table3table3 filteredtable3 filtered

I want to add a column from table3 to table1, these two tables aren't directly related.

"Client_id" in table1 is related to "Client_iid" in table2 (one in table1 to many in table2)

"ClientVragenlijst_id" in table2 is related to "ClientVragenlijst_id" in table3 (one in table2 to many in table3)

The column of interest is "text" in table3.

By filtering table 3 on "VragenlijstVraag_id" = 1832, I get the rows of interest.

Through the relationship with table2, the matching "Client_iid" can be retrieved, so it can be copied to the corresponding rows in table1.

I want to do this for mulitple values of "VragenlijstVraag_id" in table3 and I want to avoid making a new, filtered table for each different value of "VragenlijstVraag_id".

 

 

so if i understand you correctly, you have a table with unique clients, and a table with questions and answers, i.e a question id linked to a clientid and then another table with text details on the answer?  A client might have multiple questions /answers.  you are wanting to bring only one of those answers into the client table?

 

 

 

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




table1 with answerstable1 with answers

Yes that is what I mean!

Each client completed a certain questionnaire once, and I want to make a column with the answers by the clients on one of the questions from that questionnaire, as shown in the picture.

 

The manual steps that I performed to get this for one question are:

- copy table3 (with the given answers linked to the client-questionnaire_id)

- filter table3 so it only contains the answers to the desired question

- copy table2 (with the client_id linked to the client-questionnaire_id)

- merge table2 with table3 (inner join)

- merge table1 with table2 (inner join)

in your relationships view are your tables linked with a single direction or both (bi directional)?

 

if not set to both can you change that?

 

if you can, im not sure if this will work but maybe something like this

 

create a new calculated column in table 1

Text = CALCULATE(min('table3[text]), table3[VragenlijstVraag_id] = 1832)





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




When I try that, I cet the following error:

 

"A single value for column 'text' in table 'table3' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

 

The answers in the column "text" are not values, but text. I don't know if that has anything to do with it?

did you put the min in there?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Yes, I did

 

This is what I entered:

Text = CALCULATE(min('table3'[text], 'table3'[VragenlijstVraag_id] = 1832))

right ok this is something i also been struggling with lately the best way to do this, i am not sure i have all the answers.

it worked on mine but i think i have a different set of data to you.

 

options are;

1. do the merge process via power query  (as you said merge table 1 and 2 and then duplicate 3 with the filtered table to that result) - you did this already i think?

2. do a summarize in dax 

 

 

personally i would go for 1

 

 





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Okay, thank you very much for your time and help!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.