Dealing with possible ambiguous relationships

[Savil]

Hello, people!

I realize Power BI normally doesn't support multiple relationship paths between 2 tables, since it would introduce ambiguity when propagating the filters, which does make sense. With that being said, I would like to know how I could approach my issue: 

In its simplest form, my model would look something like this:

Which I use to plot the Expenses in terms of Departments and Months. Simple enough.

Here is where it gets tricky: in my company it is very possible for a Cost Center to change from one Department to another throughout the year. Thus, the relationship between Cost Centers and Department should be Calendar dependent, which in my head would look something like this:

Aaand you can see the problem.... They would be 2 relationship paths from dCalendar to fExpenseLines.

So, basically my goal is to accurately plot Expenses as a function of Departments and Months, considering that the Cost Centers that make up any specific Department might change as a function of time as well. 

Is there any way I can achieve this behavior in Power BI?

I hope I made myself clear and thanks in advance!

[Savil]

Yes, I could theoretically use PowerQuery to generate a Department column in the ExpenseLines table and assign it as you suggested.

I had never thought about considering Departments and CostCenters as 2 independent dimensions in the model, but that would probably work. I'll give it a try!

Thanks for the help!

[jdbuchanan71 · Accepted solution]

Could you add the Department to the expense lines directly and assign it based on the expense date?