Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
off course jiwhan_Kim, thanks 🙂
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] )
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
127 | |
30 | |
27 | |
24 | |
22 |