Summarize multiple tables in DAX

[zipke]

Hi all,

How can I summarize multiple tables in DAX?

I have multiple tables I want to combine and summarize in DAX, how can I do this?

I have Table1, 2 and 3.

I Summarize Table1 with this code:

New Table = 
SUMMARIZE(
    'Table1',
    'Table1'[Column1],
    'Table1'[Column2]
)

How can I add the columns from Tables 2 and 3 I want to add?

They are all linked with relationships. I have a table in the dashboard combining all of them in one big table, but as there are over 700k lines, I need them in DAX to export with DAX Studio...

Thanks in advance!

[RZAU]

I think if the table 1, 2, 3 have relateionships. You could also
Summarize(

'Table1',
'Table 1 [Column 1],
'Table 2 [Column 1],
'Table 3 [Column 2]....
)

[prithvija]

what if the tables are indirectly connected ? how can we summerize then ?
I have three tables two tables are directly connected , one is indirectly connected how can we tackle that sort of scenario ?

[Anonymous · Accepted solution]

@zipke ,

According to your description, my understanding is that you want to combine columns from multiple tables and summarize them, in this scenario, we can first use the addcolumns function to combine them, then use the summarize function to summarize them. Please refer to the following example:

Table = 
var combinetable = ADDCOLUMNS('Production Products',"CateGory Desc",RELATED('Production Categories'[description]),"ProviderName",RELATED('Production Suppliers'[companyname]))
return 
SUMMARIZE(combinetable,[CateGory Desc],[ProviderName],'Production Products'[productname],'Production Products'[unitprice])

The result will like below:

If the above DAX doesn’t help, pleases share sample data of your tables and post expected result.

Regards,
Lydia

[Anonymous]

What if i have a relationship like one-many , so which function to use instead of "Related" Dax??

[zipke]

Hi @Anonymous 

Sorry for the late reply. I just tested it and it works like a charm! Thanks a bunch!!

Can I add filters to this DAX as well? E.g. I have a column with "Status" and it should be [Status]>20.

Where can I add this to the code?

Thanks a lot in advance!