Trouble with circular type relationship

[wfarrell]

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:

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.

 Any help is greatly appreciated.

- William

[Anonymous · Accepted solution]

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.

[v-jiascu-msft · Accepted solution]

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

[Joorge_C]

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 · Accepted solution]

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]

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

[wfarrell]

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