Measure Using Relationships to create Matrix

[Anonymous]

I Know the information is out there and I have been trying to craft this for my companies budget. Essentially I have a budget based on two parameters, ( Budget Group & Account ) and I want to be able to visually stack the budget up against my total in the matrix but I cannot create a simple relationship between the two because it would make a many to many relationship. I attached a screen shot for better understanding.

The top is the data I am working with, The budget has no relationships attached to it. The others are all connected via relationships. The bottom is the matrix I want to come up with within Power Bi. I am simply having trouble setting that up.

Here is the SS, Thank you!

[jtownsend21]

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)

[Anonymous]

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!

[jtownsend21 · Accepted solution]

I have worked on a similar problem. This may or may not be an optimal solution, but I think it will work. 

1. First create relationships between the following:
   1. Main Table & Cost Element Table on the Cost Element
   2. Main Table & Employee Lookup Table on the Employee # 
2. Second Create lookup columns (I'd do this with DAX) on the Main Table to get the following: 
   1. Account from Cost Element Table
   2. Budget Group from Employee Table
3. 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)
4. Fourth create a concatenate of the Account and Budget Group in the Budget Table. 
5. 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. 

[Anonymous]

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!

@jtownsend21