Forum Discussion

TCFPA's avatar
TCFPA
Frequent Visitor
3 years ago
Solved

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 ...
Need Help
  • Barthel's avatar
    Barthel
    3 years ago

    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 

Barthel's avatar
Barthel
Icon for Solution Sage rankSolution Sage
3 years ago

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's avatar
TCFPA
Frequent Visitor
3 years ago

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's avatar
    Barthel
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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's avatar
      TCFPA
      Frequent Visitor
      3 years ago

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