Multiple filter path

[ctappan]

I've read a number of solutions that are similar to my problem, but I haven't found the answer. 

The schema:

• Customer table
• Loan History table (joined to Customer table on Customer ID and to Dates table on Calendar Date)
• Dates table (all calendar dates)
• Employee History table (joined to Customer table on Employee ID and to Dates table on Calendar Date)

This works until I get to Employee History, because the Dates table is filtering the Loan History table via two paths: a) the date in the Loan History table and b) the Employee History date > Customer > Loan History. I tried adding a distinct Employee IDs intermediary table, but that doesn't fix the multiple paths problem in PBI.

I understand why this could be problematic. However, I need to know the employee history as it relates to loan history. If an employee switches branches, I want to see their customers' loans in Branch A until the switch and in Branch B after the switch.

This seems like it would be a common requirement--do I need to have a derived table of some sort, of a combination of lookupvalue and measures...? I'm stuck.

We could theoretically build out the employee history, with branch info included, in the Customer table, but from a design standpoint that's counterintuitive and we wouldn't be able to have visuals with just Employee data as easily.

[Anonymous]

Hi @ctappan ,

Sorry for my late reply...

As the official document said, When the relationship between the tables forms a closed loop, you could:

• Delete or mark relationships as inactive to reduce ambiguity. Then, you might be able to set a relationship cross filtering as Both.
• Bring in a table twice (with a different name the second time) to eliminate loops. Doing so makes the pattern of relationships like a star schema. With a star schema, all of the relationships can be set to Both.

Or you could combine these tables.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Anonymous]

@ctappan ,

I guess that the second relationship is not active (you can notice that when you open 'manage relationships' from the model tab). 

So, you can use USERELATIONSHIP inside your measure to refer a specific calculation or function to the inactive relationship.

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

Hope that helped.