Forum Discussion
Is a relationship even possible?
Hi there - hoping that this hive can help me get my mind around a relationship issue. I am brand new to Power BI so bare with me, please.
- Office 365
-Excel Spreadsheets (6 individual ones) saved to SharePoint in MS Team
-Appended 6 queries into 1 query called MAR SCIENCE
-Added BUDGET Data Source
- Working with the 2 queries now: MAR SCIENCE and BUDGET
-BUDGET has a unique ID column and shares all the same info with MAR SCIENCE with the exception of the BUDGET column. I aam only wanting to bring in to my Power BI ta ble and charts is the Budget column and the Fundiung start ad end dates
- MAR SCIENCE I want to match the cumulative info from DIVIUSION, FUND CENTRE, FUNCTIONAL AREA, FUNDED PROGRAM and FORECAST to the same combination of info from the same named columns from BUDGET so I can sum Line Expense, show the Budget for the same line column combination. No Unique ID that would match to BUDGET
My current relationship
The result only matches the Budget to the FUND CENTRE and not the rest of the required combination which just duplicates the budget line.
Any possibilities? I Iwould then want to do a calculation to find the difference between Forecast and Budget. Any and all advice much appreciated!
2 Replies
I'm no expert, but I wonder a few things:
1) In the current relationship, is it only the FUND CENTRE that is matched? Perhaps we are seeing duplication in Sum of BUDGET because all the FUND CENTRE values are the same (at least in the screenshot)?
2) Have you tried making relationships between each one of the shared fields between those tables?3) Are the data types of these fields set correctly and identical between the two tables?
Brenden - thank you for the questions!
1. Yes it is.
2. When I try to make relationships between other fields I get an error that I can't create create a Many to Many with cross filtre direction set to both, which is what I feel I need?
3. Yes they are.
Maybe I am just not understand the correct relationship between the tables. I had thought I needed the manay to many going both ways. I stand to be corrected and educated 🙂
