Power BI Report Builder: Parameterizing DAX Queries

My team and I needed to create an easily-exportable report for our Marketing team. Our source-of-truth models are housed in Power BI Premium workspaces, and we needed to access this data from Report Builder using a custom DAX query. Unfortunately, the documentation on how to parameterize a custom DAX query is… pretty spread out and difficult to understand. Here’s how to do it, including multi-value parameter lists.

1: Create the Report Builder parameter

The Report Builder parameter is the parameter you’ll actually see and interact with when you run the report. If you’ve used Report Builder with other data sources, this is probably what you’re most familiar with.

To start, right click the Parameters folder and select “Add Parameter”.

The Parameters folder.

Give your parameter a name, prompt, and data type. The name is what you’ll use to refer to it in the report, and the prompt is what the end user will see when filling out the Parameters tab prior to running the report.

Creating the Report Builder parameter.

Optionally, you can look through the Available Values tab. In the Available Values tab, you can specify a manual list of available values, or specify a query that will generate them. I won’t cover the specifics here, as this is relatively well-documented and simple. The same is true of the Default Values tab.

Click OK, and make sure the parameter is listed under the Parameters folder. You’re done with part one!

2: Pass the parameter into your DAX query

For this section, I’m assuming you’ve already created a dataset based on a custom DAX query. If you haven’t, go ahead and create one. Right click it and select “Dataset Properties”.

Click “Dataset Properties”.

In the popup window for your dataset, select “Parameters” from the left-hand menu. Add a parameter with the same name as the one you created previously and assign the parameter you created previously as its Parameter Value. Essentially what we’re doing here is creating a “doorway” that the parameter can use to get into your DAX query. Without this, the parameter exists in the context of Report Builder, but not in the context of your dataset.

Create the parameter in your dataset.

Now, currently, we have a subtle bug. The DateTime parameter from Report Builder will be passed to your query in whatever format the user’s locale specifies. In America, that’s probably something like 12/26/2021 12:00:00. We need to standardize that. So, instead of leaving the parameter as-is, we can change the Parameter Value to an expression (click on the fx button): Format(Parameters!<ParameterName>.Value, "yyyy-MM-dd") . This will pass the value from Report Builder to your DAX query as an ISO date string. If you need the time value, you can add that to your format string.

Important: This step will likely be similar for parameters of every datatype except text. Any time the Report Builder parameter format may be different than the one DAX expects, you should format it here.

The expression for formatting your date parameter.

Done? Awesome — Part 2 finished!

3: Integrate the parameter into your query

From the Dataset Properties page, click on the Query tab, then click “Query Designer…”.

The Query tab.

In the Query Designer, click the little Parameters button at the top. It looks like a little window with an “@” overlaid.

The parameters button, highlighted at the top.

This will open a “Parameter” pane. Add the parameter here, but don’t assign it a Dimension or Hierarchy. Do assign it a default value (required).

Adding the parameter.

Okay! We’re finally ready to integrate the parameter into the query. I’m not going to cover exactly how to write a FILTER statement, as that’s well-documented DAX knowledge. However, I will provide the basics along with a screenshot of an example.

To reference the parameter in your query, use an @ sign: @StartFiscalMonthEnd . It’s probable that you’ll have to do some datatype conversion — i.e. if the parameter is coming in as a string and you need it as a date, you’d want to wrap it in DATEVALUE(@StartFiscalMonthEnd) . If you need the time as well, you could use (DATEVALUE(@StartFiscalMonthEnd) + TIMEVALUE(@StartFiscalMonthEnd)) .

An example of a working query.

And you’re done! You can now run the report and select parameter values. I hope this was helpful — and keep reading if you want to see the adjustments you have to make to use multivalued parameters.

Summary

Parameters have to be created at the Report Builder level, then passed through to the DAX dataset, and then to the DAX query.

1. Define a Report Builder parameter.
2. Define a parameter on your dataset (you can do some rudimentary formatting here).
3. Define a parameter on your dataset’s DAX query (in Query Designer).
4. Use the parameter in your query. For multivalued parameters, the TREATAS function is the best simple way — see below.

Bonus: Multivalued parameters

Need to select more than one value for a parameter? This is the section for you. For both methods, you’re going to need to follow a couple of common steps. First, when you create the Report Builder parameter (step 1 above), you need to check “Allow multiple values.”

Second, you need to change the formatting function from Step 2. What functions you use depends on which method you use: See below.

Method 1: Pipe the values together and use PATHCONTAINS

For filters that will only ever have a few values selected, this can be a good option. Essentially, you need to create a path (analogous to a filepath, but separated with pipe characters) out of the parameter selection. You can do this by replacing the function from Step 2 with the function Join(Parameters!<ParameterName>.Value, "|") . Then, in your DAX filter statement, you’d have to do something like PATHCONTAINS(@StartFiscalMonthEnd, 'Accounting Calendar'[FiscalMonthEnd]) . For each row in the Accounting Calendar table, we’re checking to see if the value (some datetime value, like 2021-01-25 ) is in the path, which would look something like 2021-01-25|2021-02-25|2021-03–25 .

Because of the number of edge cases here (what if your parameter value can have a pipe in it?), I don’t love the solution. The better solution requires a greater knowledge of DAX, but allows us to work directly with the array provided from the Report Builder parameter.

Method 2: Work directly with the parameter array in DAX

In Step 2, instead of using a function to format our values, simply pass them through to the DAX query unchanged. In the Query Designer (Step 3), be sure to check “Multiple values” in when defining the parameter. Then, in the DAX query, use the IN operator to check for list membership. This works best with text or number fields that do not require formatting at the item-level, as that level of formatting control can be difficult in DAX. Here’s a quick example:

VAR CustomFilterTable =
    FILTER(
      KEEPFILTERS(VALUES('Accounting Calendar'[FiscalMonthEnd])),
      FORMAT('Accounting Calendar'[FiscalMonthEnd], "yyyy-MM-dd") IN {@StartFiscalMonthEnd}
    )

Here’s another example using TREATAS (my preferred method):

VAR CustomFilterTable =
      TREATAS({@StartFiscalMonthEnd}, 'Accounting Calendar'[FiscalMonthEnd]

Thanks for reading!