There are two ways to get the count of products by year: Turn on bidirectional cross-filtering on the relationship. This will change how filters work for all data between these two tables as we have seen in section two. Or, we can use the function to change how the relationships work for just this measure. When using DAX, we can use the function to change how the cross-filter direction behaves between two columns defined by a relationship. In this case, the DAX expression looks like this: BiDi:= CALCULATE([Distinct Count of ProductKey] , CROSSFILTER(FactInternetSales[ProductKey] , DimProduct[ProductKey] , Both) ) By using the CROSSFILTER function in our measure expression, we get the expected results for just this measure: The use of this function opens many doors and allows you to tune the behavior per measure. Let’s look at one more example. In section four, we made sure the model doesn’t have any ambiguity, but it came at a price. By default, based on the active relationships, we count the customers who bought something in the stores region, but maybe we want to count the customers who live in the stores region. This means we need to use the non-active relationship. We can make use of a combination of and to make this happen: Customers Count Store Region:= CALCULATE([Count of customers] ,CrossFilter(Sales[CustomerId], Customer[Id], none ) ,UseRelationship (Customer[RegionId], Region[Id]) ) What this measure does is calculate the count of customers by disabling the cross-filtering on the relationship between Sales[CustomerId] and Customer[Id], and instead use the inactive relationship between Customer[RegionId] and Region[Id]. This now gives the count of customer for both paths in a single PivotTable: As you’ve seen, using DAX can give you great flexibility to turn on or off bidirectional cross-filtering per measure.
There are two ways to get the count of products by year: Turn on bidirectional cross-filtering on the relationship. This will change how filters work for all data between these two tables as we have seen in section two. Or, we can use the function to change how the relationships work for just this measure. When using DAX, we can use the function to change how the cross-filter direction behaves between two columns defined by a relationship. In this case, the DAX expression looks like this: BiDi:= CALCULATE([Distinct Count of ProductKey] , CROSSFILTER(FactInternetSales[ProductKey] , DimProduct[ProductKey] , Both) ) By using the CROSSFILTER function in our measure expression, we get the expected results for just this measure: The use of this function opens many doors and allows you to tune the behavior per measure. Let’s look at one more example. In section four, we made sure the model doesn’t have any ambiguity, but it came at a price. By default, based on the active relationships, we count the customers who bought something in the stores region, but maybe we want to count the customers who live in the stores region. This means we need to use the non-active relationship. We can make use of a combination of and to make this happen: Customers Count Store Region:= CALCULATE([Count of customers] ,CrossFilter(Sales[CustomerId], Customer[Id], none ) ,UseRelationship (Customer[RegionId], Region[Id]) ) What this measure does is calculate the count of customers by disabling the cross-filtering on the relationship between Sales[CustomerId] and Customer[Id], and instead use the inactive relationship between Customer[RegionId] and Region[Id]. This now gives the count of customer for both paths in a single PivotTable: As you’ve seen, using DAX can give you great flexibility to turn on or off bidirectional cross-filtering per measure.
Bidirectional%20cross-filtering%20in%20Analysis%20Services%202016%20and%20Power%20BI.docx