Hi All,
I feel (and hope) a simple solution is staring me in the face - and I just can’t see it. I’m looking for some help on joining two tables together using DAX. One is a calculated table so I cannot merge in the Query Editor. Following is (very) simplified version of what I’m trying to achieve.
Table 1 - Master Table (Master_AssetPurpose)
Table 2 - Detailed Cost Data (Source_Cost)
Proposed Output Table
The calculation would be
1. SUMMARIZECOLUMNS(Master_Purpose[Purpose], Master_Purpose[Sub-Purpose])
2. FILTER (Source_Cost, Source_Cost[Purpose] <> "ALL")
3. Join the Source_Cost[Item] into the result of Step 1, by matching the 2 x [Purpose] columns from tables 1 and 2
4. FILTER (Source_Cost, Source_Cost[Purpose] = "ALL")
3. Join the Source_Cost[Item] into the result of Step 1, by returning the value against all Purpose & Sub-Purpose Combinations
The result would look as follows.
It’s worth noting there’s no relationship between the first two tables as they are a many to many relationship.
All of the functions I’ve used to join the tables error because it returns multiple values. For example, Student - Type 2 would need to return A, B and D.
Any assistance would be appreciated.
Cheers
KC
Any chance of getting a file download on this one. There’s a bit too this that would require some testing I think.
Chrs
Hi Sam,
Attached is the demo model I mocked up. The actual model is more complex (ie. has multiple tables being combined dependent on multiple criteria), but also has confidential data. I hope this simpler version is ok.!
Table Merge - Demo Model.pbix (74.4 KB)
Also, as I know the relationship page often resets itself, I’ve attached a screenshot of that section noting that Table_Proposed is currently hardcoded and that is the table I’m trying to create as a result of the merge.
Thanks in advance for your help.
KC
Hi Sam,
It has occurred to me that if I change Master_Purpose to be a table calculated in the query editor rather than a calculated table using DAX, I could complete a full merge in Query Editor which would be much simpler.
I just need to wrap my head around how the “All” classification would work in that instance. I’ll keep you posted if I find a solution.
KC
No luck on the above as I couldn’t figure out how to get “A” to return as an item code against all possible Purpose scenarios (ie. if it said “All”, the “A” item applies to every purpose).
In addition, I cannot create a successful merge when I try to introduce a second master table as I need to have a common column which isn’t the case in this scenario.
It is summarised in the attached spreadsheet.
Example Merge Tables.xlsx (14.1 KB)
READ ME FIRST 
Sorry for all the updates - but I don’t want to waste your time if I am making progress. I have achieved the desired outcome but in order to do this I had to create a dummy Master table with “All” as one of the options in Master_AssetPurpose[Purpose] and Master_OperatingSystem[Operating System].
I am confident you will understand what I’ve done by looking at the Query Editor faster than I could explain it as it’s nothing complex.
This leaves me with just one question - is there a way to merge the data without having to create the dummy “withALL” tables?
Table Merge - Demo Model.pbix (89.8 KB)
SOLUTION WITH DAX
I’ve been looking at this for 3 days before I gave up and asked for help. And… now I manage to solve it myself.
Model and screenshot with the DAX code follows if anyone ever has the same problem.
Sorry Sam for wasting your time!!
Table Merge - Demo Model.pbix (101.5 KB)
1 Like
Brilliant, great solution. Really nicely laid out.
Thanks for posting this.
