I need the functional equivalent of a calculated column using two tables

[psmith-nhs-inc]

This is a typical compound key situation.  I have an item table, and each individual item has a unique ItemSKU, and multiple Items will share a MasterSKU.

For instance T-Shirts, where each individual size and color of each shirt will have it's own ItemSKU, but all sizes and colors with the same message, or graphic, will share a Master SKU, for easy sorting and rollup.

I need to to break items out midway between the two, creating  a field that is MasterSKU-Color.

The problem is Color is not in the item table, but a separate table with a one to one match (or no match, not everything has a color associated with it) that relates on ItemSKU.

A measure fails:

MasterSKU-Color = min('Item'[old_sku])&"-"&min(Color[COLOR])

Works but I can't add that measure to a Matrix Row.

A calculated column can't cross tables.

I can add color to the item table sql upon load if I have to, but I am wondering if there is a better way to achieve this within Power BI?

Phil

[v-jiascu-msft · Accepted solution]

@psmith-nhs-inc,

Hi Phil,

You can try this formula. Make sure relationship is established.

MasterSKU-Color =
'ITEM'[old_sku] & "-"
    & RELATED ( COLOR[color] )

Best Regards!

Dale

[psmith-nhs-inc]

That did it.  Thank you very much.