Design a site like this with WordPress.com
Get started

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
Advertisement

The Dos and Donts of Power BI relationships and Modelling

Bad Modelling 1: Single flat file table e.g. Salesextract

When people first start out using Power BI as their Analytics platform, there is a tendency to say, lets import all the data in one big flat file, like an Excel worksheet.

This way of working is just not well organised and doesn’t give you a friendly analytics structure.

Avoid Wide Tables

Narrow tables are much better to work with in Power BI. As the data volumes grows it will affect performance and bloat your model and become inefficient. then, when you create measures, things will start getting even more overly complex in the one long and wide table.

Not to mention the point when you have to add another table and create joins. You may be faced with the many to many join because of your wide table.

STAR SCHEMA are the recommended approach to modelling in Power BI

Stars with a few Snowflaked dimensions are also ok.

If you have a flat file wide table its always important to convert to an above data model with narrow dimension tables and a fact table in the middle with all your measures.

Remember, Chaos is a flat file.

Model Relationships propagate filters to other tables.

In this example the ProductID propagates down to the sales table. 1 Product can be sold many times. (1 to many)

With a snowflake you can add another level

CategoryA Propagates down to the Sales Fact table

Deliver the right number of tables with the right relationships in place.

Power BI was designed for the people who never had to think about the design of data warehouses. originally, this self service tool would allow any one with little or no knowledge of best practice to import data from their own sources, excel spreadsheets, databases etc without any knowledge of how they were set up.

This becomes an issue when the recommended Power BI model is the fact and dimension schemas as above.

Understanding OLAP models go a long way to helping you set up Power BI

  • Dimensions Filter and group
  • Facts Summarise measures

Bad Modelling 2: Direct Query your Transactional Database

When you connect up to OLTP and drag in all your tables ( there may be hundreds of them) using Direct Query there are lots of things to consider.

the overall performance depends on the underlying data source

When you have lots of users opening shared reports, lots of visuals are refreshed and queries are sent to the underlying source. This means that the source MUST handle these query loads for all your users AND maintain reasonable performance for those using the OLTP as they enter data.

You are not the most important person in this scenario. The person(s) using the database to add data is the most important person

OLTP is designed for speedy data input. OLAP is designed for speedy retrieval of data for analytics. These are to very different things.

With OLTP, you have row-Store indexes (Clustered Index, Non-Clustered Index) and these are slow for data analysis. They are perfect for OLTP style workloads. Data Warehouse queries, consume a huge amount of data, this is another reason why using OLTP as your direct query data source isn’t the best approach.

Also your Direct Query means you loose a fair amount of DAX functionality time time based DAX calculations, What if Parameters, etc.

I was chatting to someone about this on the forums and they gave me a fantastic analogy

When you connect into a transactional database with Direct Query, its like being in a busy restaurant and getting all the customers to go and get their food from the kitchen.

It slows down the customers because of the layout of the kitchen. They don’t know where anything is, and other customers are also milling around trying to find where their starter is.

the Kitchen staff who are now trying to prepare the food are having to fight for physical space. Look at the pastry chef, trying to work around 10 customers asking where their various desserts are?

So you set up a reporting area. This is where the food gets placed, someone shouts service and a waiter will go and speedily deliver the course to the correct table.

No one needs to go into the kitchen unless they are in food prep. Everything works in the most efficient way.

Model relationships Dos

  • Only 1 ID to One ID. If you have composite keys they need to be merged
  • No recursive Relationships (relationships that go back to the same table. the example always used for this is the managerID in the employer table
  • the Cardinality is 1 to many. 1 to 1. many to one. (Many to Many needs a specific approach in Power BI)
  • Cardinality determines whether it has filter group behavior or summarise behavior
  • There can only be one active path (relationship) Between two tables. All your other paths will be inactive (But you can set up DAX to use them)

In this example OrderDateKey is the active relationship because we use this the most and joins to DateKey

ShipdateKey and DueDateKey also join to DateKey in the date table and are inactive.

DAX Functions for Relationships to help with modelling decisions

RELATED

When creating calculated columns you can only include fields from the same table. Unless you use RELATED

For example, I’m adding the column Colour into the SalesOrderDetail table which has a Many to One join to Products •Colour = RELATED(Products[Colour])

RELATED allows you to use data from the one side in the many side of the join

RELATEDTABLE

RELATEDTABLE Uses data from the Many side of the Join

TotalSales = SUMX(RELATEDTABLE(SalesOrderDetail),SalesOrderDetail[LineTotal])

USERELATIONSHIP

Forces you to use a relationship instead of the active relationship

=CALCULATE(SUM(InternetSales[SalesAmount]), USERELATIONSHIP(InternetSales[DueDate], DateTime[Date]))

CROSSFILTER

Modifies the filter direction Disables propagation. You can actually do this in the model by changing the filter to both directions instead of single. OR you can do it for a specific DAX query using CROSSFILTER

TREATAS

Create virtual relationships between tables

CALCULATE([Total Budget],

TREATAS(VALUES(Dates[Year]),’Unconnected Budged Data'[Year]))

Our Unconnected Budgeted Data is in Year only and its not joined to our main model.

Here we connect up to Year in Date. then we can create a visal with Date from the Date dimension. Total sales from our connected data which is at daily level and Total Budget from our unconnected budgeted data at a different level of granularity.

PATH

Naturalise a recursive relationship with the PATH function

Getting your model right and understanding your data sources is the most important thing to get right with Power BI. make sure you don’t have lots of headaches six months into your project. Its better to spend the time now, than having to start again later.

Power BI Cross Filter Direction

Your Power BI Data Model contains multiple tables that need to be joined in the Relationship Pane.

Cardinality is well understood (1 to many, many to many etc) But what does the Cross filter direction mean?

Example using Azure SQL Database Adventureworks Data Warehouse

You can Create a SQL data warehouse in Azure.

Note that Sample Source has been chosen which opens up the AdventureWorksDW option

  • DimProduct
  • DimProductCategory
  • DimProductSubCategory
  • FactResellerSales

In Power BI Get Data and Azure Data Warehouse

Please note. If you are using the Warehouse to do a quick demo against, always remember to either Delete the Warehouse or Pause the Warehouse unless you want to create unnecessary costs within Azure

Login to your Server in SQL Server Management Studio with the Username and Password set up in Azure

Note, if there are issues you may need to go to the SQL Server in Azure and add your IP address to the firewall configuration.

Choose the Dims and Fact table(s) and then click OK

Then Close and Apply (Power Query Editor)

Next, In Power BI Desktop go to the relationships pane

The relationships are already set up with Cross filtering set to single.

Create a Report to test the Filtering

Add a Slicer and then go to DimProductCategory and Choose EnglishProductCategoryName for the Slicer Value

Add  Slicer 2 and then go to DimProduct and Choose EnglishProductName for the Slicer Value

Now you can select a Product Category and only those Products in that Product category will be shown

In this example I have chosen Accessories and EnglishProductName only displays Accessories

If you unselect the Category ALL products are then available in Slicer 2

If I then, for example, Choose Cable Lock from the list, Nothing happens to slicer 1. Everything is still visible

How do you enable cross filtering?

Enable Cross Filtering

Go back to the Relationships pane and click on Manage Relationshipsedit the Relationship between DimProduct and DimProductSubCategory

Set the Cross Filter Direction to Both (And tick apply security filter in both directions)

You can now select an English Product Name and Slicer 1 will only show the Category that the Product Name Belongs to

Conclusion

Cross Filtering, in the case of Slicers allows you to not only move top down but also from bottom up of the hierarchy

This is great when the reports created need Multiple slicers to select the data for the Reports