Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
I have worked on a similar problem. This may or may not be an optimal solution, but I think it will work.
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.
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!
I have worked on a similar problem. This may or may not be an optimal solution, but I think it will work.
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.
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |