Forum Discussion

jtooke's avatar
jtooke
Helper I
7 months ago
Solved

Use Power Automate to extract filtered data to Excel

Hello, I am looking to use Power Automate to allow a user to extract data from a Power BI report to Excel. The data should include any on-screen slicer selections. Due to the data size, I have been ...
Need Help
  • tharunkumarRTK's avatar
    tharunkumarRTK
    7 months ago

    jtooke 

    When you are creating a power automate flow for this use case, do not create a flow in power automate portal, instead create in power bi desktop. Otherwise, your flow might not be able to capture the selections in the power bi slicer. 

     

    To prepare the dax query easily, I would suggest you to create a table visual in your report and make sure unwanted columns are removed, columns are renamed appropriately  and totals are disabled from the visual.

     

    Place the slicer and select one or two values, Then from the performance analyzer you will be able to capture the visual dax query. 

     

    You can use the same in your power qutomate flow. This is not completed yet, as the slicer selections would be hardcoded, you can identify the section where the hardcoded values are present in the dax query and replave them with the values capture by the flow. 

     

    Note: if it is only one value then you can directly replace the value with the captured value, if there are multiple values selected in the slicer then you might need to use join/concatenate function in power automate to concatenate all the values delimted by a comma

    here is a detailed article: https://powerbi.microsoft.com/en-us/blog/unlocking-new-self-service-bi-scenarios-with-executequeries-support-in-power-automate/

     

     

     

     

    Connect on LinkedIn

    read my blogs here: techietips.co.in

     

     








    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!
cengizhanarslan's avatar
cengizhanarslan
Super User
7 months ago

1) Capture slicer selections as values in the flow

In Power Automate, the easiest way is to use the Power BI button visual (or the Power Automate visual) and pass the slicer fields as inputs.

You want to pass the selected values for:

  • RR[Group]

  • RR[Rating Type]

  • RR[Rating]

If multi-select is allowed, pass them as arrays/strings you can split.

 

2) Use a DAX query with TREATAS to apply those slicer selections

Because your slicers are on RR but you’re extracting columns from Table, you should filter RR first, then let the relationship filter Table via ID.

Example DAX query (single-select version):

DEFINE
    VAR vGroup      = GroUp
    VAR vRatingType = @RatingType
    VAR vRating     = @Rating

    VAR FilterRR =
        CALCULATETABLE (
            VALUES ( RR[ID] ),
            TREATAS ( { vGroup }, RR[Group] ),
            TREATAS ( { vRatingType }, RR[Rating Type] ),
            TREATAS ( { vRating }, RR[Rating] )
        )

EVALUATE
    CALCULATETABLE (
        SELECTCOLUMNS (
            'Table',
            "ID", 'Table'[ID],
            "Overall Inherent", 'Table'[Overall Inherent],
            "A Inherent", 'Table'[A Inherent],
            "B Inherent", 'Table'[B Inherent],
            "Overall Residual", 'Table'[Overall Residual],
            "A Residual", 'Table'[A Residual],
            "B Residual", 'Table'[B Residual]
        ),
        TREATAS ( FilterRR, 'Table'[ID] )
    )