Forum Discussion

Angith_Nair's avatar
Angith_Nair
Icon for Continued Contributor rankContinued Contributor
4 years ago
Solved

Table filtering in the reverse order.

Hello team,

Hope you are all doing well.

I am having two tables let's say table1 & table2. The relationship is something like table1 filters table2 as shown below.

I am using the measure as

Measure =
CALCULATE(
            SUM(Table1[Values]),
            Filter(Table2,Table2[Country] = "India")
)

Now according to my understandings, table2 won't filter table1 as the filter direction is from table1 to table2. But unfortunately, I am getting the sum of values as 10 for India which means table2 is also filtering table1. How is this possible?

 

Thank you in advance for your efforts...

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    4 years ago

    Hi Angith_Nair ，

    According to the official description.

    For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). For One-to-one relationships, the cross filter direction is always from both tables. Lastly, for the Many-to-many relationships, cross filter direction can be from either one of the tables, or from both tables. Notice that when the cardinality type includes a "one" side, that filters will always propagate from that side.

    And for One-to-many relationships, table expansion takes place from the "many" to the "one" sides by using LEFT OUTER JOIN semantics. When a matching value from the "many" to the "one" side doesn't exist, a blank virtual row is added to the "one" side table.

    Table expansion also occurs for One-to-one intra source group relationships, but by using FULL OUTER JOIN semantics. It ensures that blank virtual rows are added on either side, when necessary.

    The blank virtual rows are effectively Unknown Members. Unknown members represent referential integrity violations where the "many" side value has no corresponding "one" side value. Ideally these blanks should not exist, and they can be eliminated by cleansing or repairing the source data.

    The below is  how table expansion works with an animated example：

     

    And you case , if according the official description ，if your dax in sql ,just like：

    select *from tableA A 

                     left join tableB B  on A.country=B.country 

                                     where B.Country="India"

     

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

2 Replies

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Angith_Nair ，

    According to the official description.

    For One-to-many relationships, the cross filter direction is always from the "one" side, and optionally from the "many" side (bi-directional). For One-to-one relationships, the cross filter direction is always from both tables. Lastly, for the Many-to-many relationships, cross filter direction can be from either one of the tables, or from both tables. Notice that when the cardinality type includes a "one" side, that filters will always propagate from that side.

    And for One-to-many relationships, table expansion takes place from the "many" to the "one" sides by using LEFT OUTER JOIN semantics. When a matching value from the "many" to the "one" side doesn't exist, a blank virtual row is added to the "one" side table.

    Table expansion also occurs for One-to-one intra source group relationships, but by using FULL OUTER JOIN semantics. It ensures that blank virtual rows are added on either side, when necessary.

    The blank virtual rows are effectively Unknown Members. Unknown members represent referential integrity violations where the "many" side value has no corresponding "one" side value. Ideally these blanks should not exist, and they can be eliminated by cleansing or repairing the source data.

    The below is  how table expansion works with an animated example：

     

    And you case , if according the official description ，if your dax in sql ,just like：

    select *from tableA A 

                     left join tableB B  on A.country=B.country 

                                     where B.Country="India"

     

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I do not know the contents of Table2 but it looks like Table2 (like Table1) is a fact table.  If my assumption is correct, then there should be a third and fourth table called Products and Country.  These 2 new tables should be related to Table1 and Table2 (Many to One and Single).  To all your visuals, you should then drag Product and Country from these 2 new tables.

    Hope this helps.