Power BI Report Builder, DAX & Parameters

Using Report Builder Dax Parameters is not fun. We had a client requirement for some relative date filtering in a Power BI Paginated Report as part of our Enterprise PowerBI service. This is trivial in Power BI Desktop but requires some custom coding in the DAX query for Paginated Reports. Spoiler alert: we came to a dead end with our efforts but I hope this helps someone else (even if it just means giving up earlier!). Along the way we also discovered Power BI Report Builder crashes alot – like Visual Studio 2005 levels of instability – so save, and save often.

First, you need to write the query in the correct mode of Query Designer. Make sure this setting – Design Mode – is unselected:

 

This means it will keep your query as is when viewing back in the Dataset Properties view. Instead of just losing your changes or rewriting your query.

Second, if you are a DAX Studio Fan – as you should be – don’t use the formatting options if you are working with the code in that environment. Power BI Report Builder is very sensitive to the formatting of your query – especially with the RSCustomDaxFilter function – to the point where a stray Tab or Space will earn you a helpful error like “No parameter match for multivalue placeholder” or even better “Object reference not set to an instance of object”

Next gotcha is parameters. If you right click on the results grid you get the “Query Parameters” option which gives you this grid:

There’s a subtle difference to how these are configured which impacts the return result from the query.

The first option – DatesMonth – has been assigned a Dimension & Hierarchy. This means when referencing @DatesMonth in your query, it will return a hierachy like result – i.e. [Dates].[Month Name].[June]

The second option – DatesYear – has not been assigned a Dimension as so returns just the parameter value – 2022.

Normally in any expression you’ll just want the value, so be mindful of that.

Last tip is to use VAR extensively to keep your actual query as simple to debug as possible. So in our example we used:

EVALUATE

— Use VAR statements to clean and handle Parameters
VAR Month_Input = SUBSTITUTE (TRIM (SUBSTITUTE (@DatesMonth, “,”, “|”)), “| “, “|” )
VAR Year_Input = SUBSTITUTE (TRIM (SUBSTITUTE (@DatesYear, “,”, “|”)), “| “, “|” )
VAR InputDate = DATEVALUE(“01 “& Month_Input & ” ” & Year_Input)
VAR StartDate = EOMONTH(InputDate,-13)
VAR EndDate = EOMONTH(InputDate,-0)

RETURN

SUMMARIZECOLUMNS (…

This is where we reached our dead end. The query ran in query designer, validated in Dataset properties – but then when trying to run the report complained that one of the filter parameters in the RsCustomDaxFilter statements in the SUMMARIZECOLUMNS block did not exist in the Parameters collection.

It seems Power BI Report Builder is extremely sensitive to settings when querying using DAX, and unfortunately the error messages aren’t all that helpful in debugging. As we were co-fixing this problem, I sent a text file with the query that worked in my file to my colleague, and it wouldn’t work for them. Bluntly, Report Builder Dax Parameters don’t seem well supported and if at first you don’t succeed, it might not be worth persisting with.