Power automate dynamic export

[Bromik]

I have a dashboard with some different visualization for several companies financial data. In the dashboard there is a column where I can choose which companies data to include in the current visualization. By default it shows the data which is sum for all the companies. I need to export the dashboard to the pdf, and also to generate pdf with this dashboard for each company. I am trying to use power automate for that, in the flow i initialize the array with companies names in it, pass that array to the repeat for each loop, and do the export function within the loop. I pass the current value of the loop to the export function. 

With this method it generates me a pdf with the same dashboard given 5 times, the dashboard is the one with the sum of values for all the companies. 

What shall I do to make it generate pdf report with different dashboards per company? 

Thank you.

[btes]

 Could you provide the solution that best worked?

[KiranHosakote]

Hi @Bromik 

The Power Automate based solution for report bursting (PDFs sliced or filtered on a parameter like company, product, branch, etc) is complicated and not very scalable as the slicer values increase. 

If you are open to using third-party solutions, take a look at BI Helper https://bihelper.tech, an easy to use SaaS application that automates the generation and emailing of user-specific PDFs (with filters and slicers applied). It is secure, scalable and reliable and requires a single Power BI Pro / PPU license.

It doesn't require Power Automate or any other tools, and can be easily set up and run by a business user.

[lbendlin]

You need to provide the Report Level Filters. Make sure that column actually controls the data model.

[Bromik]

Yeah, I provide that filter

"filter": { "table": "Name", "column": "companies", "operator": "eq", "value": "current loop value" }. Doesn't help. How do I ensure that column controls data model?

[lbendlin]

you cannot use eq for lists of values.  use in 

[Bromik]

Thanks for the responses! As I said, I am trying to filter my report based on the values in the slicer. In the slicer there are 8 values and I need to export power BI report in PDF per every value in the slicer and send it to email. As a first step, i tried to export the report with one (instead of loop for 8 values) specific value selected ( ReportLevelFilter: Table/column eq 'Value'). However, the report which I receive in email is not filtered at all and still returns the report where all values of the slicer are selected. Do you know what may be the reason for that and how to solve it?
(Also when I tried to insert ?filter=<Table>/<column> eq <'Value'> in the URL link of my report, the page wasn't filtered.)

The flow i currently have:

 

[lbendlin]

As I said, I am trying to filter my report based on the values in the slicer. 

But you are not using the "slicer values",  you are hard coding "BBB".

To use the "Slicer values"  your Power Automate visual in Power BI must be filtered by said slicer.  The filtered values are then handed over to Power Automate. Use those values.

[Bromik]

So then I have two further questions:

1) I have a slicer with 8 possible values. Do you say that it is not possible to just get the names of those 8 values, initialize a variable with those 8 names as an array and loop through the array, checking the fact that table/column value equals to the current name in a loop, right? (So in example, if I have the column with a, b, c, d, "select all" options and i need to have 4 reports with visualiztions per each one (exluding select all), it is not enough to create an array ["a", "b","c", "d"] as value and loop through it?)

 

 

2) Do you imply that i have to somehow pass the reference to that slicer (or smth like that) to the loop and make it run through actual column? How do I do that for table "Table1"and column "Column1"?

Thank you!

[lbendlin]

1. Having that slicer means nothing by itself.  You need to convey that filter context over to the Power Automate visual.  In the most simplistic version that means that "Column1"  needs to be in the values well of the Power Automate visual.  In a more standard setup you would use fields that are required by the flow, and that are filtered by your slicer selection through the data model.

2. You do that by enumerating the values

[Bromik]

Thanks for the responce. 
1.From what I understood I need to use Column 1 (as a value of Power Automate visual in my report) in the "Apply to each" step, in "Select an output from previous step" filed (Power BI data Column1).
I did that, and now the value under "Current item" in the for loop is {"Column1":"a"} and same for b,c,d. So when writting the filter, I have Table1/column1 eq {"Column1":"a"} (I guess that works like that). How to get only the "a" for equality comparrison? 
2. Should I use "entity" somewhere?

[lbendlin · Accepted solution]

you need to check the output of the trigger but it's likely something like 

item()?['Column1']

[Bromik]

In the output of the trigger the entity is:

"Power BI values": [

                {

                    "Column1": "a"

                },

                {

                    "Column1": "b"

                },

                {

                    "Column1": "c"

                },

                {

                    "Column1": "d"

                },

                {

                    "Column1": "e"

                },

                {

                    "Column1": "f"

                },

                {

                    "Column1": "g"

                }
I used item()?['Column1'] in the ReportLevelFilters field and also for the names of the emails. The names are correct:a,b,c... (so, item()?['Column1'] worked) but the report is not filtered.

Can you please tell me what I can do incorrectly and why the report does't take into accout the field in ReportLevelFilters?

[lbendlin]

ut the report is not filtered.

Where did this come from? Are you trying to create an export that is filtered by the slicer?

[Bromik]

Yes, exactly. I am trying to create a power automate flow which will take the power BI report and generate 8 different reports based on the value in the slicer (there are 8 different values). But every time I try to filter the report in power automate it doesn't work (last time I tried to write in ReportLevelFilters field = Table1/column1 eq item()?['Column1'] as you suggested).  The report is always in "select all" state and it doesn't take into account value of the slicer which I use in power Automate.

[lbendlin]

You will want to show how you specify the report export filter.  It has to follow OData conventions, and your chosen field has to actually impact the report's semantic model. 

Fun fact: you will find that the number of daily exports is rather limited.

[Bromik]

I specify the report export filter as the usage of my slicer column in the ReportLevelFilters field in export to file step (second image). It follows OData conventions but the report that i receive after exporting is not filtered.

 

How can I check that the column used in the slicer impact the report's semantic model?

 

[Bromik]

Another think that I noticed right now is that if I filter the slicer and then save the report, I receive filtered report by the last saved slilcer values. But when I tried to clear all filters and then save this report, I have the version with selected value in the slicer and not the cleared one. When i send email with the report, I receive the latest saved version and it cannot be fileterd. 
Do you know how can I clear filters, save the report and make the power automate filter the report in export to file step? 

[lbendlin]

You would consult your data model. Check the direction of the arrows.

Your Flow code is missing the single quotes around the Power BI value.

[Bromik]

The problem is now solved! Thank you for your help!

[Justin_GR]

Hi, i have the same problem. But how did you fix it?