Circular dependency blocks cross filter

[iulian_buhat]

I have a small issue here that hopefully can be resolved.

Now, we have three tables:
Agent reports is supposed to be the "centralizer" that connects to all other tables. It is a summarization based on date, agent, branch and time slot.

Next we have Operational objectives. Each branch has in a specific time period certain objectives to meet. One might consider it's unique key the combination of start date, end date and branch. There are about 10 different target columns.

And last we have one of the summaries with actual data, Pickups and NRW, which has summaries on agent, pickup date, pickup branch and data for pickups and NRW sold.

What we want to do is tie the agent report table in a relationship with both of these and do a cross filter. I have made a unique join key to establish a relationship between agents and pickups, data is coming in no problem and filtered properly. Next we want to tie it to the objectives page to compare the actual data to the targets in objectives.

So far I have assigned a unique join key for each table to tie together, but the issue comes with the operation objectives one, since the key has to be calculated from it (basically it's a more complicated vlookup from op obj into agents, determining the right key based on branch and the date being between start and end date), but on wishing to establish a relationship based on that calculated key.... it does not want to because of a circular dependency.

I put the whole scenario to have a better idea of what I need. I don't want to fetch EVERY target into the agent table and it would not be right anyway since the comparisons would not be filtered right. All would be solved if that one relationship would establish with cross filter in both directions. But it only wants to filter agent report based on the objectives and not the other way around. It is a one to many from op obj to agents with single direction filter. I need it to be both.

[v-angzheng-msft]

Thank you for sharing😀

[iulian_buhat · Accepted solution]

Oh, nevermind, I found out how.

For those in the same problem, my solution is this:

When wishing to compare the value from agent score with the target in the other table I used this formula in a measure:

CALCULATE(max('Operational Objectives'[NRW AG.]),CROSSFILTER('Agent report'[Join Key Obj],'Operational Objectives'[Join Key Obj],Both))

The crossfilter function seems to not have the same limitations as the relationship and this formula brings the equivalent column in Op Obj for which that join key works