How to connect dashboard filters using multiple unrelated data tables in PowerBI
Nowadays, thanks in part to platforms like this one, we can find a lot of articles that refer to different functionalities and ways of acting using Power BI that serve as help or inspiration to new and not so new users of this tool. For this reason, from Bedrock, we want to do our bit by showing the process followed to solve a very specific problem that arose during the creation of one of our dashboards for one of our clients.
Statement of the problem
In this case, our client wanted to migrate a dashboard from Google Data Studio to Power BI. The motivation for this change was to homogenise all the organisation’s reports onto a single platform. As their entire media data environment was built on Azure, Power BI was the platform of choice. However, this change involved the complete transformation of the backend that feeds this dashboard. Going from having a single table created using an external tool that allows ingesting, transforming, and unifying all the data, to having multiple tables with data from different data sources without any connection between them.
Working in collaboration with the client’s technical team, the data was extracted, and the different necessary tables were created and hosted in Synapse. From there, the tables were connected to Power BI and then all the relevant changes were executed using Power Query to replicate all the existing transformations in the old dashboard.
So far, it is not very different from any other similar type of project. However, one of our client’s requests was that the filters visible on the dashboard and commonly used by stakeholders should be related to each other, and not work individually. In Data Studio, when working with a single table where the data is already related to each other, the filters already fulfil the function of filtering each other. In the case of Power BI, the data comes from different tables predefined by the client and without the possibility of modification prior to loading. As they do not have common variables and all the data sources do not have the same variables, it was not possible to initially relate them all to each other.
The hypothetical creation of a macro table with all the data was unfeasible, as each individual table has many transformations that, if carried out together with the rest of the tables, would end up giving rise to a large number of errors and incompatibilities. In addition, each table has millions of rows, so unifying all the data in a single table would not be optimal.
How could we solve this inconvenience?
What was finally decided was to create an auxiliary table combining all the dimensions needed to filter the scorecard, appending, and merging the necessary columns from each data source.
The auxiliary dimension table was created using an append query (within power query) by merging all the necessary tables and then removing all the columns that were not the dimensions used as a filter. Once this was done, multiple merges were performed to join all the equal dimensions from the different tables entered. This resulted in a table with one column for each dimension used as a filter in the dashboard.
The most optimal process would have been to perform this whole process as a direct query, however, the multiple transformations that must be performed on all the data tables to be able to operate with them prevent this option.
Once the auxiliary table has been defined, we must realise that the greatest difficulty we encounter when taking this course of action is the configuration of the joins between the auxiliary table that will serve as a filter, called DMaster from now on, and the other data tables. This is due to the following reasons:
- The set of tables used does not have a common key dimension that allows all of them to be correctly related to each other.
- We must also bear in mind that not all the dimensions used to create the filters exist in all the data tables.
- Furthermore, for this table to be used as a filter correctly, it is necessary that the union between them is one-to-many. This means that for each row in the DMaster table, there can be several rows in the other tables, but not the other way around. The DMaster rows must be unique.
How has the relationship between the tables been configured?
Taking these restrictions into account, it was decided to create a common column in each of the data tables as a key dimension, both in the data sources and in the DMaster auxiliary table, so that we can relate them to each other. This new column combines all the dimensions used as filters in the dashboard, separating each dimension with an underscore. This new key column created follows the following structure:
As mentioned above, not all data sources have the same dimensions. For this reason, missing dimensions have been created in all data tables that require them in order to achieve the same structure in all tables. All these missing dimension columns have been filled with an “X” for all rows, so that no blanks appear between the low bars that could generate errors.
These columns are created for the sole purpose of having a key dimension with a solid structure, but have no associated data. Therefore, the “X” option that appears in all filters due to this process should be excluded afterwards to avoid messing up the dashboard and/or to avoid general doubts to stakeholders, either directly in the dashboard or in a previous phase (Power Query). Below is an example of removing this value directly in the dashboard for one of the dimensions:
Finally, the way to ensure that the key dimension has unique values in the auxiliary table is to eliminate duplicates for that column. In this way, we will be able to join the auxiliary table with the rest of the data tables with a one-to-many relationship.
Once the main problem is solved, the only thing left to do is to join the tables together, where we will have the DMaster table as the core table, allowing us to connect all the tables of the dataset. In the following image, you can see how the connection between all of them looks like:
Conclusions:
Taking into account the initial conditions and the existing operational constraints, we were able to offer a solution to our customer that meets the requirements. We have provided a stable and clean solution that also allows us to have a more ordered dataset, as the DMaster table can be used as a central table where both the filters and all calculated metrics of the dashboard are stored. This makes it easier for different stakeholders to navigate through the dataset and search for metrics and/or filters.
