Forum Discussion
Measure Using Relationships to create Matrix
- 7 years ago
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:
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.
- Anonymous7 years agoNot applicable
yes! This is exactly what I was thinking about doing! I just wasn't sure how to write the DAX for step #2 . If you can help that would be greatly appreciated. If not I will try my best to figure it out
Thank you!