topic Re: Help to understand the example given in CROSSFILTER function in DAX Commands and Tips

Help to understand the example given in CROSSFILTER function

nemo — Wed, 18 Dec 2024 10:10:27 GMT

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 )?

Re: Help to understand the example given in CROSSFILTER function

shafiz_p — Wed, 18 Dec 2024 10:21:50 GMT

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!!

Re: Help to understand the example given in CROSSFILTER function

nemo — Wed, 18 Dec 2024 10:32:56 GMT

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?

Re: Help to understand the example given in CROSSFILTER function

shafiz_p — Wed, 18 Dec 2024 10:51:06 GMT

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!!

Re: Help to understand the example given in CROSSFILTER function

nemo — Wed, 18 Dec 2024 11:34:56 GMT

Thanks for your clarification shafiz_p. Much appreciated.

I think I can understand the example better now.