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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
wfarrell
Frequent 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
Anonymous
Not applicable

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.

View solution in original post

v-jiascu-msft
Microsoft Employee
Microsoft Employee

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.

View solution in original post

5 REPLIES 5
v-jiascu-msft
Microsoft Employee
Microsoft Employee

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.
Joorge_C
Resolver II
Resolver II

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.

Anonymous
Not applicable

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.

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

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.