New table based on other tables SUMMARIZE? SUMMARIZECOLUMNS?

[JoaoMS]

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:

Figure 1

The relation between them is a kind of obvious:

Figure 2

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.

Figure 3

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

[parry2k · Accepted 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.

✨ Follow us on LinkedIn and  to our YouTube channel

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!

[JoaoMS]

Thank you! it worked as expected

[parry2k]

@JoaoMS so you only new table with wo_2022 workorders, correcT?

[JoaoMS]

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

[parry2k]

@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?

✨ Follow us on LinkedIn and  to our YouTube channel

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!

[JoaoMS]

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