dax filter question, many to one

[daglite]

dax filter question, many to one

Why does in this scenario the filter appears to go from the many to the one side?

Everything I've read stated that the filters flow down hill from the one side of the relationship to the many side of the relationship.

Lets say I have two tables.

foo    bar
a       a  | b
--      -- | ------
1       1  | red
2       1  | green
        2  | blue
        2  | yellow

And two measures.

count foo = COUNTROWS(foo)
count foo filtered = CALCULATE([count foo], bar) 

When I cerate a 'Card' for each measure.

If I place a page level filter on

column 'b' of table 'bar'
and filter to color 'red'

Why does the Card with 'count foo' show 2 and the card with 'count foo filtered' show 1

I would expect both of the measures to return 2

since the filter is being applied to table 'bar'

which has a many to one relationship to table 'foo'

Instead 'count foo' still returns 2 but 'count foo filtered' now returns 1.

[Greg_Deckler]

Perhaps try changing your filter direction to bi-directional?

[daglite]

Thanks for the reply smoupre.

The weird thing is that this many to one side filtering appears to be working without bi-directional filtering...

I'm was just hoping that someone would help me understand how that is possible.

[Greg_Deckler]

From your description, the filtering is working from the One-to-Many direction and not the Many-to-One direction. I'll recreate but from what I see going on is that your first measure only includes the context of "foo". When you filter to red, because the relationship is only setup from one-to-many and not from many-to-one then the count of foo is 2 because nothing is filtering the table.

Now, when you add a CALCULATE and you include the context of "bar", then your filter applies to "bar" and thus it is calculating the count of "foo" in the context of the filter of a single "bar" value.

I'll recreate this and test but I think this is essentially what is going on.

[Greg_Deckler · Accepted solution]

I modeled this out and achieved the same results that you did. Switching the filter direction to Both caused both of the measures to act the same way when filtering by "red" (1). The only thing I can figure is that deep down in the bowls of DAX, including the filtered table in the calculation is creating an implicit relationship between them when doing the calculation even though your filter direction is not setup that way explicitly.

[daglite]

Thanks, that has to be it.