A beautiful dashboard has been created, but according to the end user of the dashboard, the data is incorrect. The cause is not clear. It can be due to several factors; or the data itself, the different parts of the Power BI dashboard , an external factor such as a page filter, a wrongly chosen visualization, an error in the calculation, an incorrect data model or a wrong step in the M-Query.
This article has been written with the aim of solving a data error message in a structured way. The article will follow the 6 steps of Power BI* from back to front. This together with tips, tricks, and advice. It is recommended to read an introduction to Power BI If you are not familiar with the 6 steps of Power BI. These can be found on the website https://kasparov-bi.nl/ or https://bi-inside.com. A short summary can be found at the bottom of this article.
How to fix an error message in a dashboard?
It’s important to go through the six Power BI steps in reverse when working on an error message (bug). Double check the choices made for each step and the connection of these choices with the previous steps. Also define the correct or expected result for the end user in advance. This will help with the research.
Below is a step-by-step plan with checks that can be carried out in the error resolution process.
1. External factors ( Filters etc.. )
Start by removing all page and visualization filters one by one. This is done to rule out that the error message is not caused by an external factor such as a visualization or page filters. Start the process of error resolution without any external factors.
Check whether the data has been (fully) loaded into Power BI if the ‘import mode’ is used. Make a reconciliation of the number of lines of example.
2. Visualization
It is important to choose the right visualization when solving error messages. Dashboards with bar and pie charts are not efficient for resolving error messages. This is because there is less insight into
underlying data such as categories and totals.
The following system rule can be adhered to: If the data does not come out well in a matrix, it will not come out well in another visualization either.
It is important to place the calculation or data in a matrix visualization. The data and totals can then be checked based on categories. Placing the data in a matrix often provides an initial insight into where the error message may be located.
Keep in mind that some visualizations, such as a pie chart, don’t support negative values. Conditional formatting can also affect data visibility.
3. DAX
A DAX calculation often consists of multiple variables or referenced calculations. If a matrix has been chosen in the previous step ‘visualisation’, the full calculation lineage can be written out. It is quite possible that either the variables do not work or that the error message appears in a previous calculation.
The following system rule can be adhered here: Try to write out the calculation in manageable and understandable parts and check each part separately.
Solution options
- The measure itself
Try rewriting it in a different way if the problem occurs in the calculation. There are often multiple ways to write a DAX calculation. - Total line
Try the SUMX functionality in combination with values* If the problem occurs in the totals. The functionality SUMX functionality affects the total.
*See article Sumx in combination with Values.
- The variablesTry to make it a separate measure or prevent the variables if the problem occurs in the variables. It is recommended though to always use variables. Variables are important for multiple reasons; Readability of the code, splitting the execution into logical steps, easier debugging of the code, better performance.
A variable is calculated only once and never again. Anything that requires (repeating) row level context, such as the SUMX functionality, will not work.
4. Data model
If the cause is not external factors, the use of the wrong visualization, or the DAX calculations then the cause may lie in the data model. The use of bi-directional relationships or incompleteness of intermediate tables can disrupt the path that Power BI takes to arrive at the right data. It is therefore very important to set up the data model according to best practices.
The following system rule can be adhered to: Check the data model for bi-directional relationships, the completeness of intermediate tables or other anomalies.
Solution options
- In the case of bi-directional relationshipA bi-directional relationship should be avoided in every possible way. The only situation in which this should occur is if one of the two tables has no other relationships. Then it is an end station and therefore not susceptible to other filtering. Always try to determine a one-to-many or many-to-many with a filter towards ‘single’ from a bi-directional relationship.
- In the event of incompleteness of an intermediate table
When using intermediate tables, it may be that it is not complete. This may be due to the data source or how the table is created in the M-Query. The best way to create an intermediate table is to reference the fact table and remove the duplicates from it. You will always have the same unique values as in the fact table.
5. Power Query
The last step before the actual data is the Power Query step. This step is used to transform the data from the data source to Power BI worthy. It may well be that this transformation has caused the data to be incorrect.
Solution options
Check the Power Query by navigating to the M-Query step where the rawest data is present (as close to the source as possible). Then in the M-Query try to reduce the data source by means of filters. If you then go through the steps, you will automatically see in which step the data from the M-Query is changed or deleted.
Tip! Hidden spaces can be solved with the trim function.
6. Data
If the above steps do not fix the error, then it is likely that the error occurs in the data itself. It is then recommended to contact the owner of the data with a summarization of the outcome of the five steps of Power BI.
Summary: an introduction to Power BI
The Power BI consultant goes through several steps when developing a dashboard
1. Data – The degree of flexibility of the data equals the risk it entails!
To be able to get started with Microsoft Power BI, it is important to think about the data source in advance and to take the following into account: The degree of flexibility of the data equals the risk it entails!
2. Power Query – A final data preparation!
The goal of Power Query is to make the data from the Power BI data source worthy. It is recommended that this largely takes place in the data source itself, but it may well be that not every action can be performed here. For example, in Power Query, one can combine data sources, add logic, and make functional changes.
3. Model – Try to create a path for Power BI to navigate as efficiently as possible.
It is important to set up the data model as efficiently as possible. Divide the different tables into dimension and fact tables. Go for one-to-many relationships and try to avoid both – on both relationships.
Try to create a path for Power BI to navigate as efficiently as possible.
4. DAX – Calculations as we know them from Excel!
DAX is the programming language of Power BI. It can be compared to formulas used in Excel. There are many different functionalities and ways to write DAX and it is recommended that you do some research on the main ones. DAX codes are used to make calculations such as sums, divide, lookups, etc.
5. Visualization – Create the insights you need!
To calculate the sum of the column […] a visualization is needed to show the result. Power BI has many different visualizations, and each visualization has its specific purpose. For example, a bar chart is very useful to show a difference in lookup values.
The data model and DAX calculation can be better adjusted if there is thought about the dashboard in advance.
6. Power BI service
In the Power BI service, one can publish reports so that it is available to end users. Here, one can also add security so that end users only see specific data. Power BI service is the end station of a Power BI development and aims to present and use the presented dashboard.
