General question - Relationship vs. Merging and the filtering problems

[kobkabnaja]

Hi Community,

I posted a quetion yesterday regarding handling data with different granularities (at this post). After working on it all day, I realized the issue might not be with joining the data, but rather with the data modeling itself. To illustrate this problem, here are the hypothetical datasets that resemble what I am working with:

Expenditure: (columns: Date, Department, Sub-Department, Code, Type, Amount)

Date	Department	Sub-Department	Code	Type	Amount
10/16/2024	A	10	10001	F	1
10/16/2024	A	10	10001	G	2
10/16/2024	A	10	10002	C	3
10/16/2024	A	11	10001	F	4
10/16/2024	B	11	10001	F	5
10/16/2024	B	11	10003	G	6
10/16/2024	C	21	10001	C	7
10/16/2024	C	21	10002	C	8
10/16/2024	C	12	10005	F	9

Budget: (Year: Date, Department, Sub-Department, Code, Type, Amount)

Year	Department	Sub-Department	Code	Type	Amount
2024	A	10	10001	F	50
2024	A	10	10001	G	10
2024	A	10	10002	C	20
2024	B	11	10001	F	30
2024	B	11	10003	G	50
2024	C	21	10002	C	35
2024	C	21	10001	C	25
2024	C	21	10002	C	15
2024	C	12	10005	F	10

Both tables are quite similar, but the Expenditure data is more detailed (daily), where as the Budget data is at the yearly level.I noticed that visualizations change depending on how I used the filter (i.e., using Department in the Expenditure as a filter v.s. using Department in the Budget as a filter).

Here are my questions:

1. Should I continue using relationships to link these datasets? If so, how can I resolve the inconsistent filtering? Do I need to create separate master tables for Department, Sub-Department, Code, and Type to manage this?

2. To create a relationship between the Budget and Expenditure tables, I created a unique ID by combining Year, Department, Sub-Department, Code, and Type. However, I also need to create a relationship between the Year column in the Budget table and the main Calendar table so I can use time-based filters. Is this the correct approach? I’ve seen few examples of people using more than one active relationship per table in Power BI, so I’m unsure if this is best way.

3. Could this issue be related to data modeling, or is it possible that I have done the modeling correctly but need to create measures to capture what I want? How would you approach this problem?

4. I also tried merging the two datasets (full join), but the results were not what I expected. Unlike in other programs, I did not get the common columns after the merge, which led to the same filtering issues. Did I miss a step during the merge, or is there a specific setting I overlooked?

I know this is a long post, and I truly appreciate anyone who takes the time to read through it. If anyone has suggestions, advice, or a solution, I’d be very grateful. I've been stuck on this for two days now, and it's becoming quite frustrating. If it’s easier to explain, I’m happy to jump on a Zoom or Microsoft Teams call. Please feel free to ask if you need any more information.

Thanks a ton!

K

[Anonymous · Accepted solution]

Hi,

Thanks for the solution @Irwan  offered, and i want to offer some more informaion for user to refer to.

hello @kobkabnaja , based on your descriiption and the output you want, i create the following model.

Here are the steps:

1.Create the following tables:

Depaerment:

SubDepaerment:

Code:

Type:

Calendar:

Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))

2.Then create the following relationships:

Department with Expenditure and Budget:one->many

SubDepaRtment with Expenditure and Budget:one->many

Code with Expenditure and Budget:one->many

Type with Expenditure and Budget:one->many

Calendar with Expenditure :one->many

3.Then calculate the following measures:

YTD = CALCULATE(SUM(Expenditure[Amount]),DATESYTD('Calendar'[Date]))

BudgetAmount = CALCULATE(SUM(Budget[Amount]),Budget[Year]=YEAR(MAX('Calendar'[Date])))

4.Then you can create a chart visual and put the following fields:

Output

If you want to create the slicer, please use the fields of the dimension table  created above.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[kobkabnaja]

Hi @Anonymous ,
Thank you so much for your help, I really appreciate it! Your solution works perfectly, except that I had to establish a many-to-many relationship between the Year column in the MASTER_CALENDAR and BUDGET tables. Initially, I solved the issue by merging the two datasets and creating new columns to match common fields, accounting for unmergeable observations. However, your approach is much more efficient and aligns better with Power BI practices.

Since I'm new to Power BI, I used to merge all datasets for analysis, but your method offers a far better way to handle multiple data sources and sets a solid foundation for future projects. Thanks again for your guidance and all the effort you have put in!

Cheers,
Kob

[Irwan]

hello @kobkabnaja 

the approach may be different from person to person. but here is my thought (might not the best)

1. i would countinue using relationship if joining the data is making another table which kinda redundant (the new one has same informations as both table). Unless you are joining the data outside PBI then import the result to PBI, then i guess that is another way.
2. Yes, i would use CONCATENATE as you mentioned above. so the relationship will be between those two unique values.

   and yes, you need another relationship for Date if you want to use time based filters (the slicer will take value from date table).

   Otherwise, if those tables is exact same but different in single column, i would just combine that value from one table to another table either using PQ or DAX then work in that one table instead of two tables.

3. Data modeling is something that need to be planned before hand esspecially if you take from multiple sources. Yes, use measure to get your desired result (although you can use calculated column as well).
4. that might be the best answer for that if you get wrong result.

Hope this will help.

Thank you

[kobkabnaja]

@Irwan  Thanks so much for addressing several key points in my questions. I eventually resolved the issue by merging and combining columns to get the correct values. Your insights were helpful, and I will definitely apply this framework in future projects. I really appreciate your help!