I recently came over an example of CROSSFILTER function in DAX. While not very common I can see a lot of potential in certain circumstances. Essentially, in a measure, it temporarily alters relationships. In most cases, there is a fact table that has many transactions related to a dimensional table with characteristics of those transactions (customers, products, dates) that is one side. With a many-to-one relationship, it is not that easy to filter one side based on the many conditions. It is possible to use RELATEDTABLE, however, CROSSFILTER is just much more elegant.
For example, if want to count unique product brands that had sales in the past few years. One way to do that would be to FILTER customer table for brands with sales and then use DISTINCTCOUNT on the Brand.
# Product 1 = VAR ProductWSales = FILTER(‘Product’, NOT(ISEMPTY(RELATEDTABLE(Sales)))) RETURN CALCULATE(DISTINCTCOUNT(‘Product'[Brand]), ProductWSales
A much nicer way to solve the same problem is to create a temporary bi-directional relationship and then count the unique brands.
# Product 2 = CALCULATE( DISTINCTCOUNT(‘Product'[Brand]), CROSSFILTER(‘Product'[ProductKey], Sales[ProductKey], Both) )
