cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Measure Using Relationships to create Matrix

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!

E2.JPG

1 ACCEPTED SOLUTION
jtownsend21
Responsive Resident
Responsive Resident

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. 

View solution in original post

4 REPLIES 4
jtownsend21
Responsive Resident
Responsive Resident

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
Not applicable

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
Responsive Resident
Responsive Resident

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. 

View solution in original post

Anonymous
Not 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!

@jtownsend21 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors