Power BI Paginated Reports based on shared Dataset : how to deal with parameter ?

[Anonymous]

Dear all, 

with the new PowerBi Premium per user, I now have the ability to use paginated reports, which sounds great.

As I have already built a dynamic dashboard on powerbi desktop, I now want to create a static, simpler paginated reporting based on the very same data.

As it is possible to base this paginated one on a shared dataset, I just followed this step-by-step official tutorial : 

https://docs.microsoft.com/en-us/power-bi/paginated-reports/report-builder-shared-datasets

(It works fine as I created a simple table visualisation on PowerBi Desktop, analyse the query and copy paste it on report Builder. )

Not straightforward indeed but it works.

But how can we deal with Parameters? 
I mean, how in a Dax perspective, can we add some "@Parameter" query parameter in such dax query starting like : 

// DAX Query
DEFINE
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
ROLLUPGROUP( etc...

I am completely stuck in this and looking at the youtube link (https://www.youtube.com/watch?v=NfoOK4QRkhI) did not help me either...

Thank you all for your help!

Philippe

PS

I have no experience on SSRS 

[Anonymous]

Hi,

Not sure if this is still relevant for you but here is the steps that I followed:
1) Create a visual in Power BI based on that data;

2) Add a filter on the visual;

3) Copy the DAX query for the visual, including the filter;

4) Connect to your PBI dataset in report builder;

5) Create a new dataset in the report builder;

6) Initialize the parameter in the Parameter tab;

7) Replace the hard-coded filter in the DAX query with your @[Parametername]. Then you're all set!

[Syndicate_Admin]

Thank you very much for the information, it has been of great help to me, I have followed in your footsteps and it has worked for me.

Now I have another question as to whether you can help me. It is that a filter that I have (Traveling Salesman) is a list with several values and my problem is that if I select a single value it shows the information well but if I select more than one value from the filter list, or if I give it to "Select All" it does not show me any information and everything goes blank.

I've tried everything but I can't get any information displayed. I attach several screenshots so that what I am saying can be well understood. I tested using the function :

I've tried the TREATAS function that comes by default in the DAX query that I've brought from PowerBi Desktop:

And I've also tried changing it to the FILTER and ALL functions:

I appreciate that alguin can help me.

Greetings and thanks in advance.

[Anonymous]

This sounds like an issue where the parameter is being treated as a "AND" filter, not as an "OR" filter. I presume you have some sort of dimensional model in place, and that technically the result will be empty if you select these two filters in an AND statement (mutually exclusive).

I think you will solve your problem if you don't compare it to equality in your filter, but to an "in" statement.The easiest way to generate an example would be to re-do the steps you did in Power BI to get the desired script out, and observe what happens when you select multiple dimensional filter values. Out of the top of my head the function will be something like "IN { @Parameter } to allow for multiples, but I suggest you check in Power BI the exact syntax 🙂

[Syndicate_Admin]

Thank you very much for your answer @Syndicate_Admin . As seen in the screenshots of my previous message, one of the tests I also performed is to put IN in the query code:

VAR __DS0FilterTable2 =
FILTER (ALL('PB-SELLER'[COD_VIAJANTE]),'PB-SELLER'[COD_VIAJANTE] IN {@VIAJANTE})

but it doesn't work for me like that either.

As you tell me, I have performed the steps again in Power Bi but this time selecting two values from the filter "TRAVELING" and in the script I have appeared the following code regarding that filter:

VAR __DS0FilterTable2 =

TREATAS({"JDELGADO","DCOMERCIAL"},'PB-SELLER'[COD_VIAJANTE])

if I leave this as it is in the report builder paginated report query, I see that it gets me out correctly and as I want the joint values of JDELGADO and DCOMERCIAL, as if I had selected them both in my filter of the paginated report. Therefore, with this I deduce that the TREATAS function works well with several values and that the problem comes from the data that reaches the @VIAJANTE parameter from the filter.

But I still don't know how to fix the problem.

That is, if I select JDELGADO and DCOMERCIAL in the report filter what should arrive at the "@VIAJANTE" parameter of the following code

VAR __DS0FilterTable2 =

TREATAS({@VIAJANTE},'PB-SELLER'[COD_VIAJANTE]) should be: "JDELGADO","DCOMERCIAL" but I do not know why, this is not so.

Thank you for your help.

[mahoneypat]

In addition to the videos out there for Power BI Report Builder, there is more content out there for SSRS which has been around for a long time (and was adapted to also work with Power BI Datasets).  Here is one video on parameters, but the whole series is very helpful to get started with paginated reports.

https://www.youtube.com/watch?v=c9bOO109bY8

Regards,

Pat

[amalrio]

@mahoneypat  This works a treat for me mate. Thanks a lot.