Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
zipke
Helper I
Helper I

Summarize multiple tables in DAX

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!

1 ACCEPTED SOLUTION
v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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:
1.png

 

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



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
RZAU
Frequent Visitor

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]....
)

 

v-yuezhe-msft
Microsoft Employee
Microsoft Employee

@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:
1.png

 

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



Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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

Hi @v-yuezhe-msft 

 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors