Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
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
Solved! Go to 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.
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
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
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.
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
85 | |
67 | |
49 |
User | Count |
---|---|
135 | |
112 | |
100 | |
66 | |
62 |