Sales comparison between dates not updating

[aaronmartin1651]

I have two tables generated by a MySQL query.  They are identical queries except for the date selected (yesterday's date and one year ago yesterday).  I transform the time column to group by hour so that I get hourly totals.  Then I linked that to a table that converts the 0-23 hour format to 12 hour format.  I also have one of the table linked to another table that converts the numerical department number to a readable format.  

My problem is that when I view the data and select various departments, only the data from the table that is linked to the department descriptions changes the other data stays the same.  I tried linking both tables to the Department table but it linked as a dotted line and did not seem to make any difference.  What am I missing?  How can I link the totals (black line) to adjust when the departments are selected?

[OwenAuger · Accepted solution]

Hi @aaronmartin1651

I suggest you change the Cross Filter Direction for all your relationships to Single (currently they are all set to Both with the <> visible on each relationship).

Then you should be able to create Active relationships between each of your fact tables to WFdata departments and Hour, and use those two tables for filtering.

I can't see any need to have bi-directional relationships.

By having all your relationships filtering both directions, you were ending up with a loop around the tables which isn't allowed, so Power BI was forcing one relationship to be inactive (dotted line).

[aaronmartin1651]

Thank you that did the trick!