Saturday 21 March 2020

Embed a Power BI tile in Power Apps with filters

Case
I want to include a Power BI tile in my Power App. Is it possible to manipulate the Power BI tile with data from the Power App?
Power BI tile in Power Apps with filter

























Solution
Power Apps has its own charts. So why would you want to use a Power BI tile in your Power App in the first place? The charts from Power Apps are pretty fast, but since Power Apps is not a reporting tool the functionality is quite basic. The two major disadvantages are:

  • The charts don't have options in the GUI for sorting, filtering or grouping. So either your Data Source is already prepared or you to do that with functions in Power Apps (Filter, GroupBy, SortByColumns, etc.). Not ideal, especially not for large datasets.
  • The charts are very basic (Column, Line or Pie chart). You could combine a column and line chart by creating two separate charts and then lay the line chart over the column chart. Again workable, but not ideal.


Let's see how you can add a Power BI tile to your Power App and what you have to do to make it more dynamic with filters.

1) Pin visual to dashboard
Power Apps can only add Power BI visuals that are pinned to a dashboard. Go to your Power BI report on PowerBI.com and click on the little pin icon above the Visual that you need in Power Apps. After that specify to which existing or new Power BI Dashboard you want to publish it.
Pin your visual to a dashboard

























2) Add Visual to Power Apps
In the Insert menu of Power Apps you see the Charts submenu in which you will find the option to add a Power BI tile. Adding it is very straight forward. Just select the right Workspace, Dashboard and then the Tile.
Adding a Power BI tile to Power Apps


















3) Adding dynamic filters
If you go to the properties of the new Power BI tile in Power Apps you will find the TileUri property. This is the URL of your Power BI tile which you can extend with filters. The standard URL looks something like:
"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d"
The DATA TileUri




















You can simply add a filter to this URL by adding an extra parameter to the querystring. Let's say we have a field called Department in the table FactInternetSales and we want to filter on the value 'Amsterdam'.
The field on which we want to filter

























Before the last double quote of the TileUri you must add &filter=[tablename]/[columnname] eq '[value]'. The result looks like:

"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d&filter=FactInternetSales/Department eq 'Amsterdam'"

Note that filter is in lowercase otherwise it won't work. For more OData like filter examples see the documentation of Power BI filters. A great tip is to first test the query string filter in Power BI itself and then copy and paste it to Power Apps. After that you can replace the hard-coded value 'Amsterdam' by a value from for example a drop drown.
Adding a Drop Down to use it in the filter

























With the drop down the URL looks like:

"https://app.powerbi.com/embed?dashboardId=11111111-aaaa-1234-aaaa-111111111111&tileId=11111111-aaaa-1234-aaaa-111111111111&groupId=11111111-aaaa-1234-aaaa-111111111111&config=abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc123abc1230%3d&filter=FactInternetSales/Department eq '" & ddDepartment.Selected.Department & "'"
If your table name or field name contains spaces, dashes or non-ansi characters you need to replace those with special characters in your query string.

4) The result
Now you can hit the play button and test the dynamic filter. It works very well, but it isn't super fast. Therefore you shouldn't have more than three Power BI tiles loaded at the same time. With the property LoadPowerBIContent you can control which tile is loading.

When clicking on the Power BI tile in your Power App the user will be redirected to Power BI online to do some more in-depth analysis. If you don't want that you need to set the PowerBIInteractions property to false (default is true).
Testing the dynamic filter

























Conclusion
In this post you learned how to use Power BI tiles, that are published to a dashboard, in Power Apps. Very straight forward to use and they have way more options than the built-in charts. One last point for attention. If you use these Power BI tiles then make sure that your Power Apps users have a Power BI licence and are authorized to the used tiles.

14 comments:

  1. Great post! Thank you for writing the stuff together :)

    ReplyDelete
  2. Hi Joost, thank you for this great post and detailed solution. Did you tried this filter workaround for "live report page" tiles? I couldn't have it worked but not sure about it is because of my mistake.
    Thanks,

    ReplyDelete
    Replies
    1. No have not tried it yet, but you could first try it within Power BI itself. Then you can compare the URL (the querystring) of Power BI to the one in Power Apps. Also check the LINK under 'Power BI filters' in step 3

      Delete
  3. Thank you for the post.
    Does the drop down needs to exists in PowerBI?

    ReplyDelete
    Replies
    1. The filter should be available in Power BI, but the filter value can come from various sources. In this example it came from a database.

      Delete
  4. Is this only in a model driven app or can you do this in a canvas app too?

    ReplyDelete
    Replies
    1. This example only shows the canvas app. However it is possible to accomplish something similar in model driven apps https://docs.microsoft.com/en-us/powerapps/maker/model-driven-apps/embed-powerbi-report-in-system-form

      Delete
  5. does it work for filtering with multiple parameter? I can seem to get it to work

    ReplyDelete
  6. Hello. I'm trying to add a visual from Power BI to my Power App. It lets me choose a Workspace and a Dashboard, but it won't let me select a tile. I currently have 4 visuals pinned to my Dashboard so it doesn't make sense to me. Any advice on how to fix this?

    ReplyDelete
  7. hi,
    I had a Power BI visual perfectly working on my PowerApp and it doesn't filter anymore. I see the visual from the Dashboard but no filtering at all. Is there any change? is anyone having this issue.

    ReplyDelete
  8. hi,

    I found the answer/solution!

    the Power BI tile has new property "AllowNewAPI" that needs to be set to true and then filtering works again as before

    ReplyDelete
  9. Does all users of Power Apps need PowerBI license as well to view the dashboard from PowerApps? Thanks a ton!

    ReplyDelete

All comments will be verified first to avoid URL spammers. यूआरएल स्पैमर से बचने के लिए सभी टिप्पणियों को पहले सत्यापित किया जाएगा।