Forum Discussion

Mestu_Paul's avatar
Mestu_Paul
Icon for Helper II rankHelper II
1 year ago
Solved

Hierarchy slicer behavior on paginated report using parameter

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!

  • 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!


3 Replies

  • 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's avatar
      Mestu_Paul
      Icon for Helper II rankHelper II

      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's avatar
        tharunkumarRTK
        Icon for Super User rankSuper User

        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!