Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
iulian_buhat
Frequent Visitor

Circular dependency blocks cross filter

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.

1 ACCEPTED SOLUTION
iulian_buhat
Frequent Visitor

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

View solution in original post

2 REPLIES 2
v-angzheng-msft
Community Support
Community Support

Thank you for sharing😀

 

 

 

 

iulian_buhat
Frequent Visitor

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.