topic Re: Get data from column from unrelated tables in DAX Commands and Tips

Get data from column from unrelated tables

stribor45 — Thu, 06 Mar 2025 01:36:43 GMT

Is there a way to get data from table B based on match between columns from A and B even thought tables are unrelated. Result is in table C. Is there are duplicates in table B ignore them and take value from "Letter" on the first encounter.

Re: Get data from column from unrelated tables

Jihwan_Kim — Thu, 06 Mar 2025 04:02:56 GMT

Hi,

Please check the below picture and the attached pbix file.

It is for creating a new table by DAX.

TABLE C = ADDCOLUMNS ( 'Table A', "@letter", MINX ( FILTER ( 'Table B', 'Table B'[id] = 'Table A'[id] && 'Table B'[code] = 'Table A'[code] ), 'Table B'[letter] ) )

Re: Get data from column from unrelated tables

stribor45 — Thu, 06 Mar 2025 13:20:39 GMT

Would you mind explaining what parts of this code executes first please. I am just trying to understand how this works. Does the table first gets filtered based on these conditions?

Re: Get data from column from unrelated tables

Jihwan_Kim — Fri, 07 Mar 2025 03:44:03 GMT

Hi,

I tried to add comments into the code like below.

TABLE C = ADDCOLUMNS ( 'Table A', // Use the ADDCOLUMNS function to create a new calculated column within 'Table A' "@letter", // Name the calculated column as "@letter" // Define the expression for the "@letter" column MINX ( // Use the MINX function to return the minimum value of the expression specified // This ensures we get the smallest (alphabetically or numerically) 'letter' value FILTER ( // Use the FILTER function to create a subset of 'Table B' // The subset includes rows where both conditions below are true: 'Table B', 'Table B'[id] = 'Table A'[id] // Condition 1: Match the 'id' in 'Table B' with the 'id' in 'Table A' && 'Table B'[code] = 'Table A'[code] // Condition 2: Match the 'code' in 'Table B' with the 'code' in 'Table A' ), // The column from 'Table B' to evaluate: 'Table B'[letter] // This is the column from which we want to extract the minimum value 'Table B'[letter] ) )

Re: Get data from column from unrelated tables

stribor45 — Sun, 09 Mar 2025 00:39:36 GMT

Thank you!!!!

If first table A had 3 more columns is there a way to somoehow only take first three (id, code and name) and then do same calculations as before?

Re: Get data from column from unrelated tables

techies — Sun, 09 Mar 2025 05:08:53 GMT

Hi stribor45 please try this

transform> add column> custom column

let
matchedRow = Table.SelectRows(#"Table b", (row) => row[id] = _[id] and row[code] = _[code])
in
if Table.RowCount(matchedRow) > 0 then matchedRow[letter]{0} else null

Re: Get data from column from unrelated tables

stribor45 — Sun, 09 Mar 2025 11:00:07 GMT

I was more hoping to use DAX as I was aware on how to do this in PQ. Also I was hoping for the table to look like this as I want to take only some columns from table A. what if table had 4 or more columns (example below) and we wanted to use only first three columns in this calculation to get same output. Is there a way for this whole code to get wrapped up with selectcolumns?

id	code	name	Day
111	222	John	Monday
111	333	John	Tuesday
333	222	Jim	Friday
444	333	Elaine	Monday

Re: Get data from column from unrelated tables

Jihwan_Kim — Sun, 09 Mar 2025 16:31:38 GMT

Hi,

It will be very helpful if I can see your sample pbix file.

Please provide your sample pbix file's link, and then I can try to look into it.

Thank you.

Re: Get data from column from unrelated tables

v-hashadapu — Thu, 24 Apr 2025 08:37:23 GMT

Hi stribor45 , Thank you for reaching out to the Microsoft Community Forum.

Please let us know if your issue is solved. If it is, consider marking the answers that helped 'Accept as Solution', so others with similar queries can find them easily. If not, please share the details.
Thank you.