Both direction filter messes up further 'logic relationship' with RLS

[Tibo_Belgium]

hi,

we hope you could help us. We search the forum & tried everything we thought of.

Problem

We expect the following result (see photo1), but once we activate the 'both direction' filter on a related table. It gives us another result.

Expected result:

the claim filters the amounts

This is the case, when we delete the 'both relationship' between contact & claim -> However we need the relationship 😉

However once we activate the both direction filter, as can be seen below. The result changes completely towards.

The claimId (of claim table), shows nothing...

The structure is as follows

user (many) -> (many) Contacts (many) <-> Claim (one) 

Claim (one) -> Amount( many)  

Thanks for your help.

[amitchandak]

@Tibo_Belgium 

does the above reply helps. if you need more help make me @

Appreciate your Kudos.

[edhans · Accepted solution]

First of all ensure 100% that the field types are the same in Power Query. Second, I'd advise you read this article on many to many relationships. They can often cause unexpected results, and often a bridge table is the better alternative.

Your bi-directional relationships can also cause unexpected issues. I try to avoid those at all costs in the model, but then activation bidirectional filtering on a measure basis with the CROSSFILTER() modifier to CALCULATE(). For example, see the following pseudo-code:

Measure =
CALCULATE(
     SUM(SomeTable[ColumnX]),
     CROSSFILTER(SomeTable[ColumnY],DifferentTable[Column4],BOTH)
     )

That leaves the model alone, but for that measure only, it does a bi-directional crossfilter on the relationship between SomeTable[ColumnY] and DifferentTable[Column4]. The relationship has to exist in the model - and can be inactive, though if it is inactive, you also have to use the USERELATIONSHIP() modifier to turn it on or CROSSFILTER still won't work. CROSSFILTER doesn't create relationships, it modifies them.