Adding multiple parameters to a DAX query as a filter in Power BI Report Builder

We may want to use multiple parameters as a filter in the DAX query while working in report builder, however, we usually do not receive the intended result.

In most cases, the filter we use in a DAX query with a parameter looks like this.

Using a parameter as a filter in a Dax query

We will not get the expected report if we try to choose multiple parameters and run the report. Instead, we were given an empty table with no rows. Even if we use the in operator instead of the = operator, we will not get the desired result.

The result when multiple parameters are selected

This problem can be fixed by following the simple steps below.

Step1 : Modify the DAX filter query by adding pathcontains function as shown below.

Dax filter query with path contains a function

Step2 : Select the dataset to which the multiple parameters will be applied as a filter. Go to dataset properties by right-clicking. Then you can select parameters. The parameter value will be as follows at first.

The Dataset Properties dialogue box

Step3 : Now update the value of the parameter as shown in the expression below. Using the | symbol, we’re attempting to split multiple parameters. To continue, click OK.

Using the | symbol to split multiple parameters

Step4 : Run the report after selecting the required values in the multi-value parameter.

The result after the changes have been made

With the values from the multi-value parameter, we can now get the expected output.

The PATHCONTAINS DAX function is used to get the preferred outcome. This DAX function checks if the provided items are present within the defined route. If the supplied object is found within the defined path, it returns TRUE. This function belongs to the Parent and Child function category.

About Us

Bi3 has been recognized for being one of the fastest-growing companies in Australia. Our team has delivered substantial and complex projects for some of the largest organizations around the globe and we’re quickly building a brand that is well known for superior delivery.

Website : https://bi3technologies.com/

Follow us on,
LinkedIn : https://www.linkedin.com/company/bi3technologies
Instagram : https://www.instagram.com/bi3technologies/
Twitter : https://twitter.com/Bi3Technologies