Issue in relationship establishment between two columns

[utsavlexmark]

Hello Team,

I am in a little complicated (for me at least) situation. I have two table to create a power bi dashboard. One table containing details about advertisement and another table details about budget. Two common fields are “Year” and “Quarter”.

The dashboard is containing most of tables and graphs from the first table, except one Gauhe Chart. That chart is being created from second table to show Budget allocation and used.

Now I am trying to create relationship between two common columns in two tables, so that these two fields can be used as a common slicer for all the tables and graphs in the dashboard. But I can’t able to create both the relationship, power bi is allowing me to have one.

Can anyone help please?

[Thingsclump]

Hi @utsavlexmark 

Here is what I suggest.

Create a common table (may be a calender). Create relationship from this table to advertisement and budget table. You can have this relationship based on granularity. (Can be at a day level or month or quarter). Once you have a slicer by a date column from common table, All others will be recalculated based on relationships and filter propogation. 

Mark it as accepted solution if it solves your issue.

Thanks

www.thingsclump.com 

[utsavlexmark]

Tried this, but the new table is allowing me to create any one relationship either "Year" or "Quarter".

Regards

Utsav

[Thingsclump]

@utsavlexmark 

Only one relationship can be active. I suggest to use quarter to create relationship between quarter column in common table (calender) and advertisement. Create one more relationship between quarter column in common table (calender) and budget. 

The common table which we have assumed as calender should have quarter column and year column. But relationship is created only using quarter column. You can create a slicer using quarter column from common table. Similarly you can use year to create a slicer. If year is selected in common table, automatically it will filters quarters for that year and inturn budget and advertisement tables.

Share dummy tables and I can show how to do it if this explanation is not clear.

We can ofcourse use userrelationship function but I think it's not necessary now.

Thankswww.thingsclump.com 

[utsavlexmark]

I have used your technique; but currently we have only data for 4th Quarter of 2021 - so I can't validate whether it is working or not. Let me check once the next quarter data will be uploaded in database - then I will get back to you and might accept your suggestion as solution.

Thanks for your help.