Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi all, I was trying to figure out how to create a new table (not in the visualization report) based on other 3 linked tables using SUMMARIZE or SUMMARIZECOLUMNS but I'm honest but got confused.
These three tables are:
1. Table "TotalWO": with a big list of registers related to Maintenance Work Orders (WO) released in our company during the past years, with their Status of execution and the Department in charge (mechanic, electrical or I&C),
2. Table "WO_2022": with the Work Orders released during this year and the week in which those WOs were executed, and
3. Table "Calendar": with columns associated with weeks and months.
In this figure I summarize these databases:
The relation between them is a kind of obvious:
And the expected output is as follows and in the same order of the columns, I mean that the first two columns should be the month and the week (from table "Calendar"), then the column "Department" (from table "TotalWO"), and so on.
I know that in the visualization report I can use the "Table" visualization to get this however I need this new table (called for instance "Final Table"), in order to use it later in a Power Automate flow.
Hope I was clear and many thanks in advance.
Joao
Solved! Go to Solution.
@JoaoMS create new table using following DAX expression:
New Table =
SUMMARIZE (
WO_2022,
'Calendar'[Month],
'Calendar'[Weel],
Total_WO[Department],
WO_2022[WO],
Total_WO[Status]
)
You can change the order of the columns the way you want.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@JoaoMS create new table using following DAX expression:
New Table =
SUMMARIZE (
WO_2022,
'Calendar'[Month],
'Calendar'[Weel],
Total_WO[Department],
WO_2022[WO],
Total_WO[Status]
)
You can change the order of the columns the way you want.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thank you! it worked as expected
@JoaoMS so you only new table with wo_2022 workorders, correcT?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Correct, it's just like the "WO_2022" table is being added with new columns but in a new table. Actually the the three tables have other columns that I'm not interested and that is why we need a new table relating just these columns, and in the order commented. thanks
@JoaoMS can you confirm the following relationship:
calendar -> wo_2022 = 1 to many, 1 on the calendar side and many on wo_2022 side
Total_wo -> wo_2022 = 1 to 1
is this correct understanding?
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k , you are right:
1. calendar -> wo_2022 = 1 to many. "Calendar" table has just 52 rows (52 weeks), but in the WO_2022 table we can find many "Work Orders" executed within the same week.
2. Total_wo -> wo_2022 = 1 to 1 . Correct, there is no duplicate.
Thanks for the quick reply
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
104 | |
102 | |
87 | |
61 |
User | Count |
---|---|
162 | |
132 | |
131 | |
95 | |
86 |