Forum Discussion
Helper IIGeneral question - Relationship vs. Merging and the filtering problems
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.
Super Userhello kobkabnaja
the approach may be different from person to person. but here is my thought (might not the best)
- 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.
- 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.
- 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).
- that might be the best answer for that if you get wrong result.
Hope this will help.
Thank you
Helper IIIrwan 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!