Hi Sam & 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
- SUMMARIZECOLUMNS(Master_Purpose[Purpose], Master_Purpose[Sub-Purpose])
- FILTER (Source_Cost, Source_Cost[Purpose] <> “ALL”)
- Join the Source_Cost[Item] into the result of Step 1, by matching the 2 x [Purpose] columns from tables 1 and 2
- FILTER (Source_Cost, Source_Cost[Purpose] = “ALL”)
- 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.