cancel
Showing results for
Did you mean:
Frequent Visitor

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

 JobName RegionID Region Name Job-0069 00038329-ac05-4f61-897a-e2ec7924a8f3 Olive Plaza Pilot

What could be happening?

1 ACCEPTED SOLUTION
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.

1. Bidirectional:
2. Measure:

``````Measure =
CALCULATE (
COUNTROWS ( Region ),
CROSSFILTER ( Region[region], Job[region], Both )
)​``````

Result

4 REPLIES 4
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.

1. Bidirectional:
2. Measure:

``````Measure =
CALCULATE (
COUNTROWS ( Region ),
CROSSFILTER ( Region[region], Job[region], Both )
)​``````

Result

Frequent Visitor

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.

Solution Sage

There may be no related names for the filtered jobs. Do you have a related region name for each job? In other words, has each Job id been assigned a Country id and each Country id a name?

Frequent Visitor

I have determined the issue. There is a massive lack of data governance at my comapny and fields are not matching up accros tables