text from a related table

[Elisa_E]

Hello, I have two related tables with a many to one relationship.

I am trying to pull the company variable into the second table using the RELATEDTABLE function and LASTNONBLANK but am getting an error. Is there another way I can proceed?  Thank you!

 

[Anonymous]

I think you need to use RELATED() instead of RELATEDTABLE().

RELATED() works on the "Many" side, and calls up to the "One" side to get the value you're looking for.

RELATEDTABLE() actually creates a table (crazy, I know) of all of the records that match the "One" side.  This table can't be visualized directly in a Power BI measure, but would be used as a parameter in another measure.

May I ask why you need the calculated column on the Many table in the first place?  Calculated columns tend to bloat the data model, and if you already have the relationship in place, you can use the "One" table in the visual, and a measure based on the "Many" table with no issues.

[Elisa_E]

Thank you for the reponse. Perfectly clear. The issue is that I need to show a table visualization with columns fom both tables, but some companies in the second table are not included in the first and therefore do not show at all. What I need is to show all companies in the second table, with blanks in the respective columns if they are not present in the first. Is a merge query possible for two tables with a many to one relationship? Thanks again!

[Ashish_Mathur · Accepted solution]

Hi,

Using the Query Editor, you should be able to use the Merge feature to bring over the Company column from Table 1 to Table 2.  I can offer more help, if you can share the download link of the workbooks.