Why crossfilter set to single works as if it was set to both?

[juan_pablo]

Why DAX is able to filter in the opposite direction of the relationship even when the crossfiltering is set to single?

Take a Iook to this simple Model:

The content of each table is as follow:

Table4

Item	qtyAssigned	Box	PO
1	5	A	X
1	5	B	X
2	10	C	X
3	3	D	Y

Table2

Item	qtyOrdered	PO
1	10	X
2	10	X
3	5	Y
4	5	Y

Table1

PO

X

Y

When I create the calculated column "Cantidad Asignada" in Table2 with the DAX formula:

'Table2'[Cantidad Asignada] = CALCULATE(SUM(Table4[qtyAssigned]))

and then I create a table visual with the result of that calculated column, it's possible to see that somehow Table4 was filtered by Table2 using the PO column, but Table2 is supposed to NOT filter Table1 nor 4 because the direction of the filter (and set to single) should block the filters obtained from the context transition generated by the CALCULATE.

Why does Table2 was able to filer Table1 and then Table4 (via Table1)?

[v-xuding-msft · Accepted solution]

Hi @juan_pablo ,

Question: Why we can aggregate the values of table 4 in table 2?

The calculation logic of your formula should be like this:

Step1, calculate the value of formula 1 and store the result in memory.

Step2, Since the table relationship is created, we know that Table4[PO] = Table1[PO] && Table2[PO] = Table1[PO], so Table2[PO] = Table4[PO] (this is dynamically executed by DAX according to the Row Context). Therefore, the return value of formula 1 is aggregated in Table 2.

(Note: If there is no relationship between the tables, you will find that the step2 can not be executed, which means that formula 1 can not complete the grouping and aggregation in table 2.)

In fact, your formula is also equivalent to the following formulas.

(Note: This is the result of manually completing row matching in the formula.)

[amitchandak]

@juan_pablo , Not very clear. But By deafult the join is right join in 1 -M. This means if there no value present on Many side that value will not come in visual .

If you need left join , You can use +0 , or Show item with no data, on the right click of PO(field in visual pane) in visual

You can use treatas to filter 4 with 2   https://docs.microsoft.com/en-us/dax/treatas-function

https://www.youtube.com/watch?v=pbAL_zG-Pgo