Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reportingdoes the above reply helps. if you need more help make me @
Appreciate your Kudos.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |