lookup through multiple tables

[jayjay0306]

Hi people,

I hope you can help me with a challenging task in making a calculated table in Power BI:

I have a data model loooking like this:

My challenge is an effort to make a calculated table looking like this:

my problem is, that in order to consolidate "Product Name" and "Customer Name" in the same table, I need to look them up through the Sales (Fact) table.

I have made an attempt in the following DAX script, where I start out with the use of a SUMMARIZE() function to build a table on the "Product"-attributes. But I can't figure out how to add the "Customer"-attributes to the table, when the relation between the two goes thorough the "Sales"-table?

This is my DAX code:

Table = SUMMARIZE('D_Product','D_Product'[Product Key],'D_Product'[Product Name],

"Customer_key", 

VAR RelatedValues_table_prod = 
    FILTER(
        RELATEDTABLE('F_Sales'),
        VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('Product'[PRODUCT_KEY]))
    )
VAR RelatedValues_table_cust = 
    FILTER(
        RELATEDTABLE('F_Sales'),
        VALUE('F_Sales'[CUSTOMER KEY]) = VALUE(RELATED('D_Customer'[CUSTOMER KEY]))
    )
RETURN
    MAXX(RelatedValues_table_cust, 'F_Sales'[CUSTOMER_KEY]),
    
    "Product key",

    VAR RelatedValues_table_prod = 
    FILTER(
        RELATEDTABLE('F_Sales'),
        VALUE('F_Sales'[PRODUCT KEY]) = VALUE(RELATED('D_Product'[PRODUCT_KEY]))
    )
VAR RelatedValues_table_cust = 
    FILTER(
        RELATEDTABLE('KPI Sell In'),
        VALUE('KPI Sell In'[CUSTOMER_KEY]) = VALUE(RELATED('Customer'[CUSTOMER_KEY]))
    )
RETURN
    MAXX(RelatedValues_table_prod, 'KPI Sell In'[PRODUCT_KEY])
)

 To be honest, I am not quite sure how to cóntinue from here?

Can it be done?

Any help or guidance is much appreciated.

Thanks.

Br,

JayJay0306

[jayjay0306]

off course jiwhan_Kim, thanks 🙂

[Jihwan_Kim · Accepted solution]

Hi, I am not sure if I understood your question correctly, but if your target to create the calculated table that looks like above, please check the below picture and the attached pbix file if it suits your requirement. It is for creating a new table.

New Table =
ADDCOLUMNS (
    F_Sales,
    "@Product Name", RELATED ( D_Product[Product Name] ),
    "@Customer Name", RELATED ( D_Customer[Customer Name] )
)