1 to Many relationship is not filtering properly.

[TCFPA]

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?

[Barthel · Accepted solution]

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 

There are some limitations to bidirectional relationships. Learn more about it here: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering 

[TCFPA]

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.

[Barthel]

@TCFPA,

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?

[TCFPA]

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