Forum Discussion

kobkabnaja's avatar
kobkabnaja
Icon for Helper II rankHelper II
1 year ago
Solved

General question - Relationship vs. Merging and the filtering problems

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, ...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
kobkabnaja
Icon for Helper II rankHelper II
1 year ago

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