Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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!

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Anonymous ,


    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

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Anonymous ,


    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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!

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

  • RZAU's avatar
    RZAU
    Frequent Visitor
    3 years ago

    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's avatar
      prithvija
      New Member
      1 year ago

      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 ?