Filter values for inactive relationships between two tables

[Anonymous]

Task is the main model, I duplicated/replicate to create Table (3). I linked both of them to Calendar table as I was preparing planned vs actual charts. Task I get Planned values and from Table (3) I get Actual values and plot them on the Calendar table date axis.

But as additional requirement, I need to create individual (plan vs actual) charts by filtering based on Area and other based on Breakdown, and Task name, etc.

To relate the tables i created a table for "Area" as common value and when i tried to give relationship between the tables only one relationship remains active.

And for the table which has active relationship the values shows correct when the filter is used.

I even used Lookup to directly relate the tables but the relationship still remains inactive. The common values between tables is Task ID but that also becomes inactive.

Is there a better way to do it? Do i have to keep creating tables for Area, then Breakdown, etc.?

[mwegener]

Hi @Anonymous ,

at first, I would remove the filter in both directions to the calendar.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[MartynRamsden]

Hi @Anonymous 

The bi-directional filters between the Calendar table and the 2 Tasks tables are preventing you from creating an active relationship between the  'Area Table' table and Tasks as there would be ambiguity in the model.

It's strongly recommended that you don't use bi-directional filters in your data model unless absolutely required.

If you can convert the the relationships to uni-directional, I think you'll be able to create an active relationship between the  'Area Table' table and Tasks. Your filters should then apply correctly.

Best regards,

Martyn

[TomMartens · Accepted solution]

Hey,

basically you should create separate tables for Area, and or Breakdown.

You should also avoid the bidirectional filtering between the calendar table and both fact tables, you might consider to use the function CROSSFILTER to turn bidi-filtering for specific measures, this will also prevent circular depencies in your data model.

I recommend to watch these videos:

• https://www.sqlbi.com/tv/different-types-of-many-to-many-relationships-in-power-bi/
• https://www.sqlbi.com/tv/understanding-relationships-in-power-bi/

and read these articles

• https://www.daxpatterns.com/budget-patterns/
• https://www.daxpatterns.com/handling-different-granularities/

Hopefully this will provide you with some ideas how to solve your requirements. If you need more help, please consider to create sample data that represents your data model and upload the pbix to onedrive or dropbox and share the link. If you use Excel to create the sample data, share the Excel file as well.

Regards,

Tom