Power BI when to (Possibly) use a Bi Directional Filter in your data model

Power BI Loves a Star Schema and It loves single direction joins, one to many from your dimension (Description) tables to your central fact (Business metrics) table

The reason for the single direction filter join? You want to be able to filter your measure by descriptors. Sales by Product category. Quantity Sold by Business Unit.

There isnt a need to filter a descriptor by a measure. it cant happen.

This is nice simple logic which works really well. However, there are some use cases where this may not apply. Lets look at our Activities model to get more of an idea of this in action.

Activities Model

• The Date table is used in lots of other models and has dates from 1990 to 2035 in it.
• This model only has 10 years of data and its important that the latest date for all the DAX and Filters is not the current date but the last date in the data
• This model is at year level and the last year in the data set is Currently 2019 (We are now in 2021)

the first thing to do is to test that the flags are working

• Year from the date table
• Flags from the flag table

And this works fine. There are only flags to 2003 to 2019 so all the years not attached to these are omitted and its all fine.

Then Activities measure from the fact table is added and it all seems to go wrong (Keys have been added to show the issue)

Note that Activities are 0

The Key (dateKey from dim to fact, FlagKey dim to fact) are essentially cross joining. There should only be one year.

If you Filter the Table so Activities are greater than 0 everything is resolved. The Fact is being filtered.

What is happening?

When the measure comes back as 0 or Null, we are not filtering the fact table

So we want to filter year by the flag but there is only a single direction filter to the factor table. Without the filter on the fact table, its almost like there is now a block in the middle, not allowing other filters to work.

You only see this because:

a. You want to see metrics that are null or 0

b. If the flag was in the date table rather than a separate dimension then there wouldn’t be an issue. Because its in another dimension it cant filter the year.

For this specific issue, the joins between date and the fact and the flag dimension and the fact has been changed from Single Direction to cross join.

Now the flag table can filter the year if we aren’t filtering the measure.

Why are cross joins not recommended in power BI?

There is always a performance issue when you do the bi directional join so you should only do this if you have to.

Bi Directional Filters are one of the top Power BI performance Killers because Power BI has to work much harder with that join

Other Ways to Resolve the issue

Resolve in SQL

The Flags are specific to the report date NOT the current date. But, if the Date dimension was created in SQL Pre Power BI, these flags could have been added in the Date table. Then simply imported into Power BI within the Date dimension

Resolve in Power Query Editor in Power BI

• With the Date Dimension and the flag dimension still intact in Power BI.
• In Power Query Editor, Merge Flags into date,
• Add in the Flag for the last 10 years into the date table.
• Then Filter the date table so there is only 10 years worth of dates.
• This would ensure you didn’t need to cross filter join so long as the criteria is that you want to see 10 years of data