Help to understand the example given in CROSSFILTER function

[nemo]

The example for CROSSFILTER was given as follows:

DEFINE

    MEASURE Sales[Customers] =

        COUNTROWS ( Customer )

    MEASURE Sales[CustomersFiltered] =

        CALCULATE (

            [Customers],

            CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH )

        )

    MEASURE Sales[ProductsDoesNotFilter] =

        CALCULATE (

            [Customers],

            CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH ),

            CROSSFILTER ( Sales[ProductKey], 'Product'[ProductKey], NONE )

        )

EVALUATE

SUMMARIZECOLUMNS (

    'Product'[Brand],

    "Customers", [Customers],

    "Customers Buying", [CustomersFiltered],

    "Products does not filter Sales", [ProductsDoesNotFilter]

)

which returned the below result.

I could understand the values of the first 2 columns in the above table, but had no idea why the third column had the value of 18,869.

Would someone please explain what happened, in particular what was the purpose for this line of code CROSSFILTER ( Sales[ProductKey], 'Product'[ProductKey], NONE )?

[shafiz_p]

Hi @nemo ,

The line CROSSFILTER ( Sales[ProductKey], 'Product'[ProductKey], NONE ) disables the filtering effect of the Product table on the Sales table. This means that any filters applied to the Product table will not affect the Sales table.

In the SUMMARIZECOLUMNS function, the third column "Products does not filter Sales" uses the Sales[ProductsDoesNotFilter] measure. Because the relationship between Sales and Product is disabled, the count of customers is not affected by any filters on the Product table. This results in a higher count (18,869) because it includes all customers, regardless of any product-related filters.

Hope this helps!!

If this helps, please accept it as a solution and a kudos!!

[nemo]

Thanks for your quick response @shafiz_p. 

Yes, I understand the line CROSSFILTER ( Sales[ProductKey], 'Product'[ProductKey], NONE ) disables the filtering effect between tables Product and Sales, but the other line CROSSFILTER ( Sales[CustomerKey], Customer[CustomerKey], BOTH ) still applies a filtering effect between tables Customer and Sales, thus the third column/measure should have returned the same values as the second column/measure, don't you think? What's the point of having both CROSSFILTER in the third measure?

[shafiz_p · Accepted solution]

Sales[CustomersFiltered]: This measure considers the bidirectional relationship between Sales and   Customer. If there are any filters applied to the Product table, they will still affect the Sales table indirectly through the Customer table.

Sales[ProductsDoesNotFilter]: This measure explicitly disables the relationship between Sales and  Product. This means that any filters applied to the Product table will not affect the Sales table at all. However, the bidirectional relationship between Sales and Customer remains active.

The key difference is that the third measure isolates the Sales table from any filtering effects of the Product table, ensuring that only the relationship between Sales and Customer is considered where as the second measure considering filtering effects of the product table indirectly.

Hope this clearify the confusion!!

[nemo]

Thanks for your clarification @shafiz_p. Much appreciated.

I think I can understand the example better now.