Hierarchy slicer behavior on paginated report using parameter

[Mestu_Paul]

Hi everyone,

I'm currently working on a Power BI report that I share with my clients. To handle tables with a large number of rows, I need to export this report as a paginated report.

In the Power BI report, users can filter the table using a hierarchy slicer. I want to replicate this functionality in the paginated report so that the exported version respects the user's slicer-based filters. However, I am facing challenges in creating or managing parameters in the paginated report that mimic the hierarchy slicer behavior.

For example:

Col1    Col2    Col3
 A1      B1      C1
 A1      B2      C2
 A1      B3      C3
 A2      B1      C4
 A2      B2      C5 
 A2      B4      C5 

In the hierarchy slicer, Col1 represents the first level and Col2 represents the second level.
For instance, if a user selects A1 from the first level and B4 from the second level, the table filters down to:

Col1    Col2    Col3
 A1      B1      C1
 A1      B2      C2
 A1      B3      C3 
 A2      B4      C5 

How can I replicate this filtering behavior in the paginated report? Is there a way to set up cascading parameters or a similar mechanism that would allow me to dynamically filter the report based on user selections in the Power BI report?

Any guidance would be greatly appreciated!

[tharunkumarRTK]

@Mestu_Paul 

Have you embedded your paginated report in a power bi report? If yes, then you would have created a hierarchical slicer in power bi report. You can bind the the parameters in your paginated report with their respective columns in power bi. 

Checkout this video for more info.

https://www.youtube.com/watch?v=5Fcf8eXRq2E

Within your paginated report dataset query, you can use these parameter values to build the logic. From the example you shared, logic is to use sort of OR condition based on two parameter values. 

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[Mestu_Paul]

No I dont embed the paginated report on power bi report, directly I try to export using rest api. However first of all I need to design the paginated report in reports builder with parameters. I can manage basic filter or range filter but problem with hierarchy filter.

[tharunkumarRTK · Accepted solution]

@Mestu_Paul 

In this case, I dont think you need to create a paginated report to export the data. You can use Executive dax query api. This will executive the dax query and and can extract the result.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group

To make your user expereince better, you can use power atuomate flow and build the flow and embed this flow as a button in your power bi report. So that from your power bi report user will make the selections and clicks on the button and then he/she will receive the data as a data export.

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

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!