Showing Shortcut Dimension 3 in Purchase Invoice Line table

[algomine]

Hello,

I have these two tables:

1. Posted Purchase Invoice Lines: This one includes a field called Dimension_Set_ID. This table also contains ShortcutDimension1 (called KÖLTSÉGHELY) and ShortcutDimension2 (called PROJEKT):

2. Dimension Set entries: This table also contains the Dimension_Set_ID field. It captures all the unique dimension value combinations. For example, I have three dimension codes set in BC, and unique dimension value combination gets a new number (called Dimension_Set_ID)

My goal is to display the ShortcutDimension3 (which is called AKTIVÁLÁSI KÓD in the DimensionSetID table) in the PostedPurchaseLine table. I have made an attempt by using LOOKUPVALUE:

ShortcutDimension3Code =

LOOKUPVALUE(

'bc DimensionSetEntries'[Aktivalasi_Kod],

'bc DimensionSetEntries'[Dimension_Set_ID],'bc PostedPurchaseInvoiceLineExtended'[DimensionSetID])

I have added this column which would only display the ShortcutDimension3 code values, but no use:

But I get this error: "A table of multiple values was supplied where a single value was expected."

The problem is that there is many-to-many relationship between the two tables (DimensionSetID), so I could not establish a relationship:

 Can someone help me how I can display the ShortcutDimension3 values in the table PostedPurchaselines? I am stuck with this for a while now.

Thank you!

[Anonymous]

Makes little sense to do such things in DAX. Do it in Power Query, where this calculation truly belongs.