Replace category with Category from another table

Hi Anonymous ,

I treid to create some dummy data for reference.

The relationship:

WIND_FAULT_CATEGORIES_GADS:

WIND_FAULT_CATEGORIES_IEC:

The scenario on my side is that I want to check which shops my fruits or vegetables are sold in the GADS table.

If my sample data above is similar with yours, let's move on.

You can't use RELATED function in table GADS but you can use the RELATED in table IEC because of the relationship. In table GADS, a GADS code will have multiple IEC codes associated with it, which causes an error that if you use the RELATED function in table GADS , you cannot return a single value.

This is a reproduction of the issue.

To solve this, we can merge tables in Power Query.

Merge the Tables:

In Power BI Desktop, go to the Home tab and click on Merge Queries.
Select the Products table as the first table and the Categories table as the second table.
Merge them using the matching columns.

Expand the Merged Table:

After merging, you will see a new column with a table icon. Click on the expand icon (two arrows) next to this column.
Select Shops column and click OK.

At this time, you are very close to success, and you will get an error below when you close and apply, which is good to delete this relationship and it will be loaded successfully.

If you want DAX instead of the transformation in Power Query. You can create a calculated table with DAX to implement it.

Table = FILTER(CROSSJOIN('WIND_FAULT_CATEGORIES_GADS','WIND_FAULT_CATEGORIES_IEC'),[GADS Code]=[IEC code])

With such a calculation table, the result can be obtained.

The easiest way I think is just drag the fields from tables, and it will show the result because of your relationship connected.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.