Power BI testing automation with DAX studio – automated testing of Power BI reports use case

BI & Data Science Analyst at 10 Senses

These days, we live in a digitized, data-driven, and highly dynamic business environment. As a result, the need for accurate and reliable insights is more crucial than ever.

These can be delivered by the most efficient Business Intelligence (BI) tools. Such BI systems and BI tools manipulate and bring together data from different data sources. Consequently, they provide business users with valuable knowledge about their industry, customers, competition, and forecasts for the future.

The leading business analytics tool is Microsoft Power BI. It allows organizations to visualize data and gain comprehensive insights with BI and analytics reports. They can use these BI tools to make educated strategic decisions and gain a competitive advantage in the market.

Nevertheless, with increasing data complexity and dashboard configurations, the manual testing and validation of most BI tools can be challenging, time-consuming, and prone to human error.

Therefore, the automation of Power BI testing has emerged as a critical process in the Business Intelligence solution lifecycle. By automating Power BI testing, organizations can ensure the accuracy, reliability, and timeliness of their Power BI reports while significantly reducing the effort related to manual testing.

Let’s then go through a real-world use case demonstrating how testing automation in DAX Studio (or other technologies) can streamline Power BI testing procedures, thereby improving overall data quality and boosting business decision-making processes.

Power BI report project baseline

The project was to streamline and enhance the way the customer visualizes, interacts with, and makes decisions based on sales data. The end goal was to create a dynamic, condensed Sales Dashboard in Microsoft Power BI in place of many BI reports dispersed in different areas of the organization.

Power BI project goals

One of the Power BI project goals was to enable business users to explore a wide array of sales metrics. This entailed:

  • overall sales value,
  • sales quantity,
  • customer count,
  • individual product performance.

Moreover, one of the key features of the Power BI solution entailed defining row-level security (RLS security) for different groups in the organization.

Power BI project stakeholders

The project stakeholders ranged from:

  • managers who needed real-time data to monitor sales activities and the performance of various products,
  • executives who required a high-level overview of sales trends to inform strategic decision-making,

to:

  • individuals in an organization to have an overall overview of performance vs. targets instead of using various reports.

Power BI project phases

During creating test cases it is important to get feedback from end users about priorities

The Power BI project entailed a few phases, among them:

  • Power BI data model preparation (based on multiple data sources),
  • Power BI dashboard development (including metrics, data visualizations, and row-level security),
  • testing.

Pre-testing phase

In fact, the Power BI testing phase started before the testing itself due to the necessity of:

  • fully understanding the complexity of the source data, the Power BI data model, key features, and all the relationships between each piece of report data and metrics,
  • getting to know the Power BI dashboard inside-out to be able to conduct end-to-end testing of the most critical parts of the Power BI report,
  • creating accurate test plans for automated and manual experiments (like performance testing, regression testing, or data security validation),
  • choosing the most efficient BI tools for testing automation, compare options and choose the best one for this specific use case.

In fact, there are multiple Power BI report testing tools on the market, such as BI Validator, or Wiiisdom. Nevertheless, due to time and budget limitations, the testing team chose DAX Studio.

DAX Studio is not one of the BI tools for traditional test automation of Power BI reports. Nevertheless, although it doesn’t have all the advanced functionalities of most BI tools for testing, it can streamline the testing process and speed up the end-to-end testing of Business Intelligence reports.

During the pre-testing phase, the testing team needed to closely collaborate with the Power BI dashboard developers and the stakeholders to get insights about the source data, Power BI data model, and dashboard and to define the priorities for the testing phase.

Different types of test plans in Power BI testing

As a result, the testing team was able to create more than 500 test plans. All of them fulfilled the need of testing the dashboard from multiple perspectives, such as:

  • functional testing,
  • regression testing,
  • security testing,
  • performance testing (for example, the performance in the case of concurrent user load)
  • tolerance and stress testing.

If you want to know more about Power BI testing types, you can read our article about Business Intelligence tools here.

Power BI testing automation setup

With numerous tests already created and priorities agreed upon with the stakeholders, the pool of experiments was divided into two groups:

  • manual testing,
  • automated testing.

Manual testing is necessary in some cases

It is worthwhile keeping in mind that some of you cannot automate specific tests. In these cases, you need to adopt the typical approach. Therefore, Power BI testers need to manually test certain functionalities and features, or compare visual data, for example:

  • “Verify that the whole report has a consistent layout”,
  • “Verify that the report font, color, and style are set as expected and are the same across all pages”,
  • “Verify that the report can be filtered by different parameters”,
  • “Verify that the page loads without errors”,
  • “Verify that there is interactivity between all visuals in the report”.

Therefore, testers need to manually test certain key features of a Microsoft Power BI report. It includes data consistency, the quality of visual data and accuracy of specific data visualizations, row-level security, and performance testing (for example, loading time when concurrent user load).

Refining testing cases before automated Power BI testing

On the other hand, you can automate tests with metrics validation, data correctness, and error checking. Such a solution comes with multiple benefits. These benefits entail primarily time savings, reduced risk of human errors, and the possibility of conducting end-to-end testing.

Nevertheless, to carry out automated Power BI testing effectively, you need to get the expected data from accountable stakeholders.

Data needed to automate Power BI testing in DAX Studio

As for the Sales Dashboard, we were able to get data about sales value, sales quantity, profit from previous years.

Only by having access to the database, which is also a data source for the main Power BI report, or files with correct data validated by the accountable people in the organization can you perform automated tests and confront the Power BI dashboard’s actual pieces of data with the expected ones.

In fact, in most cases, once you get the data, you will have to refine the test cases you created in the previous step. You will either change them, add new ones, or add details to the existing ones.

Examples of Power BI testing cases

For example, if you have data from previous periods:

  • If it was originally “Verify that sales value show correct values”, you can add periods to the test, like “Verify that sales value for FY2021 is $2M”, or “Verify that sales value is $20k for April 2022”,
  • If it was initially “Verify that sales metrics show true values”, you can refine it to “Verify that sales value in metrics is $1.4M for FY2022”.

Or, if you have data about specific customers:

  • If it was originally “Verify that sales quantity show correct values”, you can change it to “Verify that sales quantity is 867 for customer X002 for FY22”.

Therefore, the task is to adjust the data in the list of “automated” tests to the data that you get. The task here is to cope with what you have and use it to the fullest in the testing process.

Final preparation for automated BI report testing

In the Power BI project, the testing team disposed of data concerning Sales Value and Sales Quantity from the previous fiscal years, and individual performances against historic targets.

The testing group also had access to the SQL database (it can be another ODBC-enabled data source), which was one of the main data sources for the main Power BI report, Sales Dashboard.

The task for the testing team was to familiarize themselves with the data, set priorities with the stakeholders, and adjust testing cases. As a result, the team knew which measures and filters in the report were most crucial for the end users. Then they were able to modify certain test plans to fulfill the requirements for the BI testing.

The last step of the testing automation setup was to choose one tool to carry out automated Power BI testing. In our use case, the team used DAX studio. It is an open-source tool used for optimizing DAX queries but can also be used for testing Power BI reports.

If you want to familiarize yourself with other Power BI testing automation options, check out our list here.

Power BI automated testing steps – summary

Let’s sum up all the steps in the testing automation setup of Power BI reports to provide you with a checklist that can be useful in your Business Intelligence software testing projects:

  1. Create test instances, including all the test types, such as functional testing, regression testing, stress testing, or performance testing.
  2. Label each test “manual”, or “automated”, depending on the possibility of automation.
  3. Get the correct historical data from the accountable stakeholders or access relevant BI reports with the data.
  4. Add details or refine your tests to adjust them to the validated data you got.
  5. Choose the best BI testing automation tool for your specific Business Intelligence testing use case.

Power BI automated testing in DAX Studio

In DAX Studio, the testing team connected to the data model that the Sales Dashboard in Microsoft Power BI was based on and that was downloaded from the Power BI Service. Then, they started with creating a base for our automated testing, which was such a table:

Automated Power BI testing - creating a table in DAX studio - Power BI testing automation

The goal was to end up with a table with columns, such as:

  • Test Name,
  • Expected value,
  • Actual value,
  • Test Passed.

The Test Name and Expected value were directly taken from the list of testing cases.

However, for Actual value they had to use corresponding measures from the report data for each test and apply appropriate filters. Lastly, the Test Passed column was filled with “Yes” or “No” depending on whether Expected value was equal to Actual.

Examples of queries in DAX studio for Business Intelligence (BI) tools testing

An example code in DAX studio used for Power BI solution testing looked like this:

EVALUATE
ADDCOLUMNS(
UNION(
ROW(
    “Test Name”, “Sales value January 2023”
    ,”Expected value”, 700000
    ,”Actual value”
    , ROUND(
        CALCULATE(
            [Sales_Value],
            ‘Calendar'[Year_Month]=”2023/01″,
            FILTER(
                VALUES(‘INVOICES'[INVOICEACCOUNT]),
                COUNTROWS(
                    FILTER(
                        ‘CUSTOMERS’,
                        ‘CUSTOMERS'[ACCOUNT] = ‘INVOICES'[INVOICEACCOUNT]
                    )
                ) > 0
            )
        ),
        0
    )
)
,ROW(
    “Test Name”, “Sales quantity April 2021”
    ,”Expected value”, 1010
    ,”Actual value”
    , ROUND(
        CALCULATE(
            [Sales_Quantity],
            ‘Calendar'[Year_Month]=”2021/04″,
            FILTER(
                VALUES(‘INVOICES'[INVOICEACCOUNT]),
                COUNTROWS(
                    FILTER(
                        ‘CUSTOMERS’,
                        ‘CUSTOMERS'[ACCOUNT] = ‘INVOICES'[INVOICEACCOUNT]
                    )
                ) > 0
            )
        ),
        0
    )
)
)
,”Test Passed”, IF([Expected value]= [Actual value], “Yes”, BLANK()))

As you can see, the Test Name and Expected values were passed to the DAX query. As for Actual value, a measure from the data model was passed, and relevant filters were applied that were also part of the data model.

Looking at the query, you can easily guess that the test was “Verify that sales value for January 2023 is 70000”.

Another example can be:

In this case, the initial testing case was “Verify that sales quantity in April 2021 is 1010”.

Truth be told, you can create as many tests as you need, applying different filters and using various measures. Obviously, if the Power BI data model is complex and the testing phase is time-limited, you won’t be able to check every single piece of data.

Nevertheless, with DAX Studio, you can easily test the most critical measures, along with a few randomly picked ones.

The final effect of such BI testing is such a table:

Why use DAX Studio for automated Power BI testing?

As you can see, it can serve as:

  • A solution to speed up the end-to-end testing process of BI reports,
  • A tool to identify areas of inconsistency in BI reports, for example, in our use case, we can see that the Profit margin for FY 2021 is not equal in Expected and Actual results, so this area would need further investigation,
  • A form of test validation of BI reports that can be shown in the project documentation or during meetings with a customer or other stakeholders of the project,
  • A baseline for creating Power BI reports with tests for the end users, enabling them to add their own tests in the future.

Power BI report based on the test cases

As the last phase of the Business Intelligence testing phase, the team created an automated Power BI report. It showed examples of testing cases and allowed the customer to add their own tests in the future.

The data model of this Business Intelligence report was a composite data model. The team connected it to the original dataset, but they added a table created from the code from DAX Studio:

Then, they created a page with the table and instructions for end users, empowering them to add changes:

Composite model of original Power BI data and test cases table

It is worth mentioning a few limitations of composite models in the Microsoft Power BI platform:

  1. Power BI admins need to turn on specific settings to enable the creation of composite models in Power BI, just like in this Microsoft article.
  2. You can add new tables or measures to the original data model. Nevertheless, if you use measures from the original dataset, you won’t be able to refresh the composite dataset from the Power BI Service workspace level. Instead, you will have to download the pbix file and refresh it on the Power BI desktop. The data model won’t refresh automatically along with the original dataset.

Keep in mind, though, that if you use historic data in Power BI testing, there is no need to refresh the testing BI report data as it should not change.

Power BI testing automation in DAX Studio – summary

Summing up, automated testing of Power BI reports and Power BI dashboards in DAX Studio is not a perfect solution. Nevertheless, it can automate the BI testing of data and the calculation dimension of a Power BI data model.

A great benefit of DAX Studio is that Power BI testers don’t need to spend time learning a new coding language. All they need to know are a few DAX queries.

What is more, despite its simplicity, such Business Intelligence tools testing automation solution can significantly speed up the process of testing Power BI and analytics reports testing and minimize the risk of human errors that can easily occur during manual testing.

It is also easy to visualize the testing outcome for the customer or other stakeholders. It can be a Power BI report, that you can easily connect to the original BI dataset.

Therefore, such a solution to automate Power BI testing in DAX Studio is a free and easy solution to automate Power BI testing without being obliged to invest in costly Business Intelligence automation tools.