Power Platform Solution and Power BI – Part 3

In Part 1 we added Power BI to the Power Platform solution and then in Part 2 we added a dashboard. This is post we will embed the report into a form and filter the report to match the context of the form. For this we will use the Accounts form and filter the expenses report to only show the expenses related to one account.

Series

• Part 1 – Add Power BI dataset and report to solution
• Part 2 – Show report in a dashboard
• Part 3 – Embed report in a form and add context filtering

YouTube Version

Embed the report into a form.

In my Power Platform solution, the Accounts table has one form included. When I edit the form I can see that I previously added a tab for expenses. On that tab there is an empty section, ready to insert the report.

In the left hand side menu, select components and expand Power BI. Drag and drop Power BI report into the blank section. An pane appears asking lots of questions regarding showing various parts. For now leave all of them as default and click Done. The unfiltered report should appear.

Filtering the embedded report.

The report needs to be filtered to only show the expenses of the selected account. With the report selected you will get the properties pane showing on the right hand side. Expand Components and click on Power BI Report. In the pane that appears, same as before find JSON filter string. You need a JSON string to put in the Static value.

The filter we are going to apply is straight forward match the account id on the form with the account id in the report. We need 3 bits of information

• Power BI Report table name – Account
• Power BI Report column name for the account id – AccountID
• Dataverse logical column name for account id – accountid

You must get the case and of course the spelling right. We then insert these into a json string. The whole string must be on one line, no returns and tabs to make it pretty. Below is an image to explain the parts and below that is the code I used.

{"Filter": "[{\"$schema\":\"basic\",\"target\": {\"table\":\"Account\",\"column\":\"AccountID\"}, \"operator\":\"In\",\"values\":[$a],\"filterType\":1}]", "Alias": {"$a": "accountid"}}

When editing a form in Dataverse, the form is in new record mode so if you have the filter right the report should filter to nothing. When it is working click Done and Save and Publish your form.

Testing the form

Make sure you test your form. Open your app, open an account and look at the expenses and the report. If your report is a direct query report you should be able to add a new record and the report update.

Conclusion

The three posts combined gives us the building blocks to include Power BI in our Power Platform Solutions. I’m hoping more Power BI artefacts such as dataflows also get included in time. There will be future posts, let me know what you want me to do.

Here are the resources I used

About Power BI in Power Apps Solutions – Power BI | Microsoft Learn

More Power BI Posts

• Conditional Formatting Update
• Data Refresh Date
• Using Inactive Relationships in a Measure
• DAX CrossFilter Function
• COALESCE Function to Remove Blanks
• Personalize Visuals
• Gradient Legends
• Endorse a Dataset as Promoted or Certified
• Q&A Synonyms Update
• Import Text Using Examples
• Paginated Report Resources
• Refreshing Datasets Automatically with Power BI Dataflows
• Charticulator
• Dataverse Connector – July 2022 Update
• Dataverse Choice Columns
• Switch Dataverse Tenancy
• Connecting to Google Analytics
• Take Over a Dataset
• Export Data from Power BI Visuals
• Embed a Paginated Report
• Using SQL on Dataverse for Power BI
• Power Platform Solution and Power BI Series
• Creating a Custom Smart Narrative
• Power Automate Button in a Power BI Report

Power BI Series

• SVG in Power BI series
• Power BI and Project Online series
• Slicers series
• Dataflow series
• Power BI SVG series
• Power Automate and Power BI Rest API series
• Power BI and DevOps series