Power BI – DAX CrossFilter Function
In this post I am continuing my campaign against bi-directional relationships. This is an introduction of the CROSSFILTER function, which can be used to simulate the bi-directional relationship in a calculation.
The Problem
Yesterday I posted regarding Cascading Filters and removing the need for bi-directional joins to filter slicers. Another reason I’ve seen users using the bi-directional filter is to do a calculation.
Using the same example as yesterday, see the link above, I created a measure to count the number of products.
Then added a card to count the number of products. With no filters applied it shows 7 products which matches the table and the slicer.
Selecting Daisy Delights from the Shop slicer, the table reduces to 5 rows, and after yesterday’s post, the Product slicer also reduces to 5 rows, but the Product Count card still shows 7.
The big bad bi-directional join would be one method to solve this, but that is not good practice and I’m not suggesting that.
CROSSFILTER Function
The CROSSFILTER function can be used within a CALCULATE function to specify a direction for relationship to be used between 2 tables. The options for the directions are None, One or Both. So I use this in the measure
Now when I select Daisy Delights on the report, the measure shown in the card changes to 5. The card value matches the other filtered elements.
Conclusion
Keeping data models as simple is vital to allow for reports to be kept maintained in the future. CROSSFILTER function is a great addition to the options for calculating measures.
Microsoft’s documentation can be found at https://docs.microsoft.com/en-us/dax/crossfilter-function
More Power BI Posts
- Conditional Formatting Update
- Data Refresh Date
- Using Inactive Relationships in a Measure
- DAX CrossFilter Function
- COALESCE Function to Remove Blanks
- Personalize Visuals
- Gradient Legends
- Endorse a Dataset as Promoted or Certified
- Q&A Synonyms Update
- Import Text Using Examples
- Paginated Report Resources
- Refreshing Datasets Automatically with Power BI Dataflows
- Charticulator
- Dataverse Connector – July 2022 Update
- Dataverse Choice Columns
- Switch Dataverse Tenancy
- Connecting to Google Analytics
- Take Over a Dataset
- Export Data from Power BI Visuals
- Embed a Paginated Report
- Using SQL on Dataverse for Power BI
- Power Platform Solution and Power BI Series
- Creating a Custom Smart Narrative
- Power Automate Button in a Power BI Report
You can also write Product Count = Calculate(Count(Products[ProductNum]),Sales)