Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
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]....
)
@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
What if i have a relationship like one-many , so which function to use instead of "Related" Dax??
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |