Forum Discussion
Measure Using Relationships to create Matrix
I have worked on a similar problem. This may or may not be an optimal solution, but I think it will work.
- First create relationships between the following:
- Main Table & Cost Element Table on the Cost Element
- Main Table & Employee Lookup Table on the Employee #
- Second Create lookup columns (I'd do this with DAX) on the Main Table to get the following:
- Account from Cost Element Table
- Budget Group from Employee Table
- Third, create a concatenate of the Account and Budget Group on the Main Table. (You could consolidate steps 2.1, 2.2 and 3 into a single step with the right DAX)
- Fourth create a concatenate of the Account and Budget Group in the Budget Table.
- Fifth create the relationship between the Main Table and Budget Table using those concatenated columns.
If I understand your dataset correctly this should give you a 1 to many from the Budget Table to Main Table (respectively). If you need help specifically with the DAX just let me know. I didn't include it to save time in case you already know how to write those.
- First create relationships between the following:
Certainly. Lookup functions in DAX can feel awkward compared to Excel's vlookups.
The Account lookup:
Account Lookup =
LOOKUPVALUE(
'Cost Element Table'[Account],
'Cost Element Table'[Cost Element],
'Main Table'[Cost Element]
)The Budget Group lookup:
Budget Group Lookup =
LOOKUPVALUE(
'Employee Lookup'[Budget Group],
'Employee Lookup'[Employee #],
'Main Table'[Employee #]
)The Concatenate:
Account & Budget Group =
CONCATENATE(
'Main Table'[Account Lookup],
'Main Table'[Budget Group Lookup]
)Consolidated would be the following (though I have never put a lookup inside a concatenate so I am not 100% certain. it may have some unintented performance issues with a large dataset).
Account & Budget Group =
CONCATENATE(
//Account lookup
LOOKUPVALUE(
'Cost Element Table'[Account],
'Cost Element Table'[Cost Element],
'Main Table'[Cost Element]
),
//Budget Group Lookup
LOOKUPVALUE(
'Employee Lookup'[Budget Group],
'Employee Lookup'[Employee #],
'Main Table'[Employee #]
)
)An alternate way of doing this that is probably the better way if you can edit the data sources would be to merge the Cost Element Table and Employee Lookup Table with your Main table. Then just expand the columns you need. The concatenates for the Main Table and Budget Table can also be done in the Querry Editor.
Using this method would be better for performance as all these steps are done before your data is loaded.
If your not familiar with this, then I recomend it. 1 it's a better way to do it. 2 its a greay way to get familiar with a really powerful tool in PowerBI. This 2 minute video shows how to merger querries (Really straightforward)
Thank you so much! I got it all set up. Now I just need to figure out how to set up my tables and then I make it pretty and its good to go. Hope you have a great day!