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,
I have data in 2 tables, I need to join these 2 tables on multiple columns by writing a DAX formula.
These 2 tables don't have connection or relationship in the data model. These are independent tables.
Also I don't want to merge these tables in Power query. My requirement is to achieve through DAX.
How can i get Table 2 details along with corresponding sales values in Table 1 by joining on multiple columns.
Join conditions will be
Table1.Year = Table2.Year
Table1.Category = Table2.SubGroup
Table1.Region = Table2.Location
Table1.Product = Table2.Product
Table 1 data :
Table 2:
Expected result just showing few rows but ideally need to show for all possible rows.
Please see below links of attached data and pbi files.
PBIX file:
https://www.dropbox.com/s/y9k3glcsmztxdqx/Sample%20File.pbix?dl=0
Table 1:
https://www.dropbox.com/scl/fi/33u4ma5hccjwq26w5b039/Sample-Table-1.xls?dl=0&rlkey=r3qvvq7rg8lr6preh...
Table 2:
https://www.dropbox.com/scl/fi/wcfz9vsttvmua3ghmil1o/Sample-Table-2.xls?dl=0&rlkey=206oaq0lnosh39dst...
Thanks in advance
Dee
Solved! Go to Solution.
you can try this
Column = maxx(FILTER('Table 1','Table 1'[Year]='Table 2'[Year]&&'Table 2'[Subgroup]='Table 1'[Category]&&'Table 1'[Location]='Table 2'[Location]&&'Table 1'[Product]='Table 2'[Product]),'Table 1'[Sales])
Proud to be a Super User!
you can try this
Column = maxx(FILTER('Table 1','Table 1'[Year]='Table 2'[Year]&&'Table 2'[Subgroup]='Table 1'[Category]&&'Table 1'[Location]='Table 2'[Location]&&'Table 1'[Product]='Table 2'[Product]),'Table 1'[Sales])
Proud to be a Super User!
This isn't working for I have the same columns in two tables I have tried the formula with and without quotes
what's the error message?
and you missed the second parameter.
Proud to be a Super User!
Oh man didn't even see that, work great thank you.
you are welcome
Proud to be a Super User!
you are welcome
Proud to be a Super User!
@Deeintu
you can use UNION Function
Union (
Summarize (Table 1, required columns in speicific order),
Summarise ( Table 2, required columns same speicific order above)
Both Table columns order should be same
Proud to be a Super User!
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 |
---|---|
107 | |
94 | |
77 | |
63 | |
50 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |