Recently, the P3 Adaptive Power Apps team spent some time participating in thought sessions around the topic of the symbiotic relationship between Power Apps and Power BI. Specifically, the team discussed when one platform should be the primary host as there are integration options that work in both directions. After working through the options with our team of experts, we felt compelled to share some tips on enhancing implementation and configuration between Power Apps and Power BI.
Compiled below are the thoughts, research findings, and a few bits of recent conversations with our P3 Adaptive team. We believe this will be helpful for anyone who come across hybrid solutions. A fully resolved Example Scenario is included as well.
Power BI in Power Apps is best if
- the solution is more focused on inputting or editing data as opposed to viewing or exploring data
- you only need to pass limited data from Power Apps to Power BI through filters
- this can only be done through URL string manipulation
- it is possible to build a URL string with 10 or more filters based on various Power Apps data, but it will be tough to build and even more difficult for someone else to maintain or troubleshoot down the road
- you don’t need to edit or save data in a Power BI dataset
Power Apps in Power BI is best if
- the solution is more about viewing or exploring data, with minor data entry or process related functions
- you want to edit data in the PBI dataset
- using Direct Query for instant report updating, if using Import Mode, a Power Automate flow may be required to refresh the data ahead of the next scheduled refresh
- you need to pass a lot of data from Power BI to Power Apps
- search criteria are numerous, dynamic, and/or inconsistent
- there are performance concerns with direct data source connections (you can leverage the Power BI dataset for initial viewing)
The client has the following requirements:
- Data sources – two existing SQL databases on separate servers, self-hosted and geo-replicated
- Search Part Design database to find similar parts based on 1-10 user-defined criteria
- E.g., Outside Diameter > 4.2 and Material contains “stainless”
- Select 3-part numbers from Part Design database
- Search ERP database for cost data on selected 3-part numbers
- Display side-by-side cost data for 3-part numbers as well as visuals such as cost versus time, gross margin versus time, etc.
- Generate a cost estimate output based on reviewed ERP data and manually input factors such as cost multipliers and quantity break discounts
Example Scenario Recommendation – Power BI reports with embedded Power App
The logic behind the recommendation:
Requirements 2, 3, and 4 could possibly be done within Power Apps however, building a search function for a variable number of differing criteria and passing this information to a SQL stored procedure, then receiving and parsing the results would require a significant amount of front-end work. Even after that initial investment of time, a challenge would remain of passing selected part numbers from Power Apps to the ERP-based Power BI reports which would need to be done through report URL string manipulation for the filter variables. Displaying side-by-side data from the ERP system could be done in Power Apps, but the other requested visualizations for the 5th requirement likely could not.
Irrefutably, requirements 2-5 would be best achieved through Power BI dataset connections to both databases with multiple filters and slicers to perform the Part Design searching. Selected part numbers could then be easily used with the ERP dataset to filter the side-by-side report and other visuals. Power BI datasets refreshed on a regular basis or via Direct Query would also improve performance compared to a live Power Apps SQL data connection via an On-Premises Data Gateway.
Requirement 6 is the only real input/creation part of the solution. It is best suited for Power Apps where the data would be passed from Power BI to an embedded Power App so that other manual data could be entered to produce the final output document.
While hybrid applications are growing in popularity, it may not always be so clear as to which method best supports the requirements. Generally, logic will dictate which method will work best but in cases where there doesn’t seem to be a clear choice, you can always reach out to us for expert assistance.
Need help with the build start to finish? Just need help on strategy and solutioning? Our P3 Power App experts are the best in the business and ready to put the power in your hands.
This blog was created in collaboration with Ed Bellman, an expert P3 Adaptive Power Apps Principal Consultant.
Power BI in Power Apps References:
- Power BI tile control in Power Apps – Power Apps | Microsoft Docs
- Filter a report using query string parameters in the URL – Power BI | Microsoft Docs
- Display Power BI Dashboards and Reports in Power Apps (mssqltips.com)
Power Apps in Power BI References: