SUMMARIZE, SUMMARIZECOLUMNS, GROUPBY for multiple tables

[Anonymous]

Hi,

I have 2 separate tables in Power BI model and I want to combine them into 1 table.

Here is the result I want to see by using DAX expression:

The table format for TableA and TableB is different and they are not exactly the same.

How can I use either of the SUMMARIZE, SUMMARIZECOLUMNS or GROUPBY functions to do it with DAX? I tried all of them but seems not running properly with the expected result.

I want the result to display in the data tab (not the report tab) in Power BI Desktop.

Thanks.

Best regards,

Emily

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

First, create relationship between the two tables with [ID] column.

Second, create a measure:

Measure 5 = MAX(Sheet5[Qty]) + MAX(Sheet6[Qty])

But, if your the second table like this:

You need to create a calculated table:

Table = 
UNION(
    Sheet5,Sheet6
)

If you want to create fact table, you can do like this:

Create two calculated tables:

Table = 
UNION(
   SELECTCOLUMNS(Sheet5, "ID", Sheet5[ID], "Qty", Sheet5[Qty]),
   SELECTCOLUMNS(Sheet6, "ID", Sheet6[ID], "Qty", Sheet6[Qty])
)

Table 2 = 
SUMMARIZE(
    'Table',
    'Table'[ID],
    "Qty",
    SUM('Table'[Qty])
)

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

I'd suggest that you rename the third column in both tables to Item.  Then use the Append functionality in the Query Editor to combine both datasets.

Hope this helps.

[amitchandak]

Try like

summarize(
union(
selectcolumns(Table1,"ID",Table1[ID],"QTY",Table1[QTY]),
selectcolumns(Table2,"ID",Table2[ID],"QTY",Table2[QTY])
),[ID],"QTY SUM",sum([QTY]))

Appreciate your Kudos.

[Anonymous]

@amitchandak 

I tried your proposed answer but I got the following error message, can you please assist?

Many thanks!

Best regards,

Emily

[amitchandak]

Is not suggesting [QTY] (With square bracket)

Otherwise, try

Table =
var _tab =union(
selectcolumns(Table1,"ID",Table1[ID],"QTY",Table1[QTY]),
selectcolumns(Table2,"ID",Table2[ID],"QTY",Table2[QTY])
)
return
summarize(_tab,[ID],"QTY SUM",sum([QTY]))

 Appreciate your Kudos.

[Anonymous]

@amitchandak 

No.....revised one still not working...

Can you assist? Thanks.

Best regards,

Emily

[amitchandak]

Try like

Table =
var _tab =union(
selectcolumns(Table1,"ID",Table1[ID],"_QTY",Table1[QTY]),
selectcolumns(Table2,"ID",Table2[ID],"_QTY",Table2[QTY])
)
return
summarize(_tab,[ID],"QTY SUM",sum([_QTY]))