cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
wfarrell Regular Visitor
Regular Visitor

Trouble with circular type relationship

Hello,

 

I am running into an issue when using multiple relationships that appear to be in a circular pattern.  I have four tables total, Production Table & Leads Table which are not unqiue, while the Date table and Employee Roster tables are unique.  Below is a sample of those tables:

 

Capture.PNG

 

I created the following relationships

 

  • Date[Date] 1------>* Production[LockedDate]
  • Date[Date] 1------>* Production[FundedDate]
  • Employee Roster 1  <-------->* Production[Employee] 

I am using the DAX formula (CALCULATE(COUNT(Produiction[LockedDate]), USERELATIONSHIP(Date[Date], Production[LockedDate]) to calculate the number of Locks and Funds. I have to use the Employee field from the Employee Roster table to group by, due to some inconsistencies in the Production table.

 

My issue starts when I bring in the Leads table and try to create a relationship between the Leads table and the Date table and the Leads table and the Employee Roster table.  Right now, all I get is a COUNT of all of the leads per person, but I cannot dynamically filter by date.  

 

For example, if I was to filter date[date] to 1/6/18 I should only see 1 lead for Steve, but it shows the two total leads for Steve contained in the Leads table.  Below is what my relationships look like in Power BI. Please keep in mind that there are more date fields that I created a relationship for that I excluded from the example above.

 

Capture3.PNG

 

 

 

 Any help is greatly appreciated.

 

- William

2 ACCEPTED SOLUTIONS

Accepted Solutions
ChrisHaas Established Member
Established Member

Re: Trouble with circular type relationship

Remove the Bi-directional filters between Employee Roster and the two fact tables (Production and Leads).  I'm guessing you're getting some unexpected cross-filtering, especially since you're using USERELATIONSHIP() to filter on a different date column.

Community Support Team
Community Support Team

Re: Trouble with circular type relationship

Hi @wfarrell,

 

I would suggest you change the "Cross filter direction" to Single and activate the relationship between Date and Leads. Can you share a sample file, please? Maybe we can help more. Please mask the sensitive parts first.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5
ChrisHaas Established Member
Established Member

Re: Trouble with circular type relationship

Remove the Bi-directional filters between Employee Roster and the two fact tables (Production and Leads).  I'm guessing you're getting some unexpected cross-filtering, especially since you're using USERELATIONSHIP() to filter on a different date column.

wfarrell Regular Visitor
Regular Visitor

Re: Trouble with circular type relationship

Hi Chris,

 

Thank you for the timely response. I'm still running into issues when I add the number of leads to the matrix.  I'm unable to filter by the date[date] column.  All I get is the total number of leads per employee, but I cannot filter it by date[date].  I think the issue is beacuse of the relationships between Leads[Employee] <----- Employee Roster[Employee] and the relationship between Leads[LeadDate] <----- Date[Date].  I'm only picking up the relationship between Leads[Employee] <----- Employee Roster[Employee].

 

Let me know if I should try to clarify this better.

 

- William

Joorge_C Member
Member

Re: Trouble with circular type relationship

Hello, You could try merging the Leads Table with the Employee roster table in P.Query to bring the wanted field and avoid the relatioship issue.

wfarrell Regular Visitor
Regular Visitor

Re: Trouble with circular type relationship

To further clarify, I am trying to calculate the number of leads from the leads table based on two seperate lookup tables, which are Date and Employee Roster.  It only seems to work with the Employee Roster lookup table, but not the Date lookup table.

 

- William

Community Support Team
Community Support Team

Re: Trouble with circular type relationship

Hi @wfarrell,

 

I would suggest you change the "Cross filter direction" to Single and activate the relationship between Date and Leads. Can you share a sample file, please? Maybe we can help more. Please mask the sensitive parts first.

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.