Forum Discussion
Optional & Multi-Select Paginated Reports Query Parameters with Analysis Services Data Source
Hello Power BI community! I need help with parameters and paginated reports. I want to generate paginated reports from within Power BI using the Paginated Report Visual as the container. I'm using Analysis Services as a data source and need to employ DAX for the datasource.
Since "report filter parameters" are slow, I prefer using "query parameters" for faster performance. My goal is to use Power BI slicers to send multi-select data and optional slicers from the Power BI page. This way, if a user doesn't make a selection, it defaults to "All." I'm struggling to find clear information or tutorials on implementing this technique.
If you know of any tutorials creating parameters using DAX, please share. I appreciate any assistance. It seems that while Power BI incorporates SSRS, connecting them via parameters isn't as seamless as expected. Thanks!
1 Reply
A few pieces make this work end to end with the Paginated Report Visual on top of an Analysis Services (DAX) source:
1) Multi-select - build the dataset in Report Builder's DAX query designer (don't hand-write the query). Drag the field into the Filters pane and tick "Parameter". Then open the parameter and set "Allow multiple values". The designer auto-generates the DAX using RSCustomDaxFilter, which already handles a list of values, so you don't have to write the multi-value logic yourself.
2) Optional / default to "All" - give the report parameter a Default Value. The simplest reliable pattern is to default it to all available values, so if the user makes no selection on the Power BI page the report still returns everything. (Adding an explicit "(All)" available value and handling it in the query also works, but defaulting to all members is the least fiddly.)
3) Binding from the Power BI page - in the Paginated Report Visual you map each report parameter to a field/slicer on the page. A multi-select slicer then feeds the multi-value parameter; if nothing is selected, step 2's "default to all" is what keeps it from breaking or returning nothing.
So it isn't slicer magic - it's (a) "Allow multiple values" on the parameter, (b) the query designer's auto-generated multi-value DAX filter, and (c) a default value for the optional case. That dataset-level @parameter route is exactly the "query parameter" approach you want, and it's faster than report-level filtering as you noted. If you share how your slicer is wired to the visual I can be more specific.
