Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
TCFPA
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

TCFPA_0-1672437757956.png

 

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

TCFPA_5-1672438297785.png

 

I am unsure why this is, as the region_key are all distinct

 

TCFPA_2-1672438055623.png

 

So for example 

TCFPA_4-1672438251982.png

 

this job despite having one region ID it is getting a count of 6 regions

 

Id instead expect the following

JobNameRegionIDRegion Name
Job-006900038329-ac05-4f61-897a-e2ec7924a8f3Olive Plaza Pilot

 

What could be happening?

 

 

 

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

Barthel_0-1672479378656.png

Because the jobs do not filter the regions, the total of the regions is shown everywhere.

Barthel_1-1672479426103.png

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:
    Barthel_3-1672479610041.png
  2. Measure:

 

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

 

Result 

Barthel_4-1672479766210.png

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

View solution in original post

4 REPLIES 4
Barthel
Solution Sage
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. 

Barthel_0-1672479378656.png

Because the jobs do not filter the regions, the total of the regions is shown everywhere.

Barthel_1-1672479426103.png

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:
    Barthel_3-1672479610041.png
  2. Measure:

 

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

 

Result 

Barthel_4-1672479766210.png

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

@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
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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.