Forum Discussion
1 to Many relationship is not filtering properly.
I have this going on with two separate relationships but for the sake of this question we will focus on one.
I have a Fact table full of a given services info and a separate region lookup table
There are only six regions and IDs. The job table has a Job ID which is correlated to a specific region
i.e. Job-2000 is region 1000
I would expect that when I add the JobID and the region name I would get
Job-2000 LA West
However that is not happening when I test a count on the data in the matrix one region ID is returning multiple region names
I am unsure why this is, as the region_key are all distinct
So for example
this job despite having one region ID it is getting a count of 6 regions
Id instead expect the following
| JobName | RegionID | Region Name |
| Job-0069 | 00038329-ac05-4f61-897a-e2ec7924a8f3 | Olive Plaza Pilot |
What could be happening?
Hey TCFPA,
Your relationship is now set up so that the one-side filters the many-side, not the other way around. You can recognize this by the direction of the arrow in the relationship.
Because the jobs do not filter the regions, the total of the regions is shown everywhere.
You can do two things: 1. make the relationship bidirectional default 2. create a measure in which you make the relationship bidirectional specifically for that calculation.
- Bidirectional:
- Measure:
Measure = CALCULATE ( COUNTROWS ( Region ), CROSSFILTER ( Region[region], Job[region], Both ) )Result
There are some limitations to bidirectional relationships. Learn more about it here: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering
- Bidirectional:
4 Replies
- Barthel
Solution Sage
Hey TCFPA,
Your relationship is now set up so that the one-side filters the many-side, not the other way around. You can recognize this by the direction of the arrow in the relationship.
Because the jobs do not filter the regions, the total of the regions is shown everywhere.
You can do two things: 1. make the relationship bidirectional default 2. create a measure in which you make the relationship bidirectional specifically for that calculation.
- Bidirectional:
- Measure:
Measure = CALCULATE ( COUNTROWS ( Region ), CROSSFILTER ( Region[region], Job[region], Both ) )Result
There are some limitations to bidirectional relationships. Learn more about it here: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering
This solved one problem but not a new one. If I drop region name from the region table into a slicer it will not slice the data instead I just get blank rows. This confuses me as I thought the slice provides filter context which flows down to the table. If I drop items from upstream elsewhere in my model the slicer works just not this region field.
- Bidirectional:
