• Lukas Paskauskas

Power Apps visual in Power BI report using Live Connection

By its nature, Power BI is a data visualization and reporting tool. User feedback and data input is not the primary goal of Power BI. However, Power BI offers an option of integrating Power Apps applications inside your reports. Combination of these two tools allows Power BI consumers to look for quick data insights and take data-driven actions, all at the same time, while browsing a single report.


Power Apps visual is out of the box feature in Power BI. It receives data from a report in real-time so users are able to interact with other visuals and immediately see context changes in Power Apps.


In this post we will be working with Power BI report using Live connection to tabular model hosted inside Azure Analysis Services. Although Power BI, as of now, does not allow to combine Live Connection mode together with other modes in the same data model, you will see that Power Apps visual is capable of connecting to multiple other data sources regardless of data source in Power BI report.


Scenario

This post will cover a business case when Power BI consumers need an intuitive option to record data values, as well as user provided comments, while browsing a data quality report related to product information.

Same solution can be applied to any kind of report which primarily serves for reviewing business operations and requires to record user comments and other relevant data inputs.


In this tutorial we will cover

  1. Preparation of Power BI report using Live Connection mode

  2. Connecting to Power Apps visual

  3. Preparation of data storage to keep records of user input

  4. Configuration of Power Apps application

  5. How to retrieve and display history of user input


Preparation of Power BI report using Live Connection mode


Large organizations very commonly implement their own analytical databases, either tabular models or multidimensional models, to enhance data analysis and reporting experience on top of massively growing data amounts. Power BI provides a great option to directly connect to a data model deployed on the Analysis Services database. Such approach allows Power BI consumers to immediately start visualizing data and look for insights without worrying about underlying data model and role-based security. Power BI inherits data model and all of its relationships directly from Analysis Services database.


You can read more about benefits and use cases of a Live connection in Power BI.


To begin with, let's connect our Power BI report to Azure Analysis Services database.


1. In Power BI Desktop, under Home tab, click on Get data option


2. In the newly opened window, let's choose Azure category, then select Azure Analysis Services database and click on Connect button.

Get Data - Azure Analysis Services database

3. Next, we need to provide a server name and optionally a database name of our Analysis Services instance hosted on the Azure platform. In our example, we are going to use adventureworks database, which is a sample data model from Microsoft and it can be deployed to your Analysis Services instance at a time of its creation.

Please note, that in order to establish a Live Connection we must choose Connect live option.

Once all information is provided, continue by clicking OK button.

Connect live - SQL Server Analysis Services database

One of the main differences of Live Connection mode, compared to other modes, is highly limited number of transformations and data modeling options that we are able to perform in Power BI. You can notice that most of the buttons in the menu ribbon on top are disabled as well as Data view page on the left side of our report. This behavior can be expected, because once Power BI establishes a Live Connection to Analysis Services, no actual data is being imported into Power BI so only metadata and other relevant information about Analysis Services database is stored in .pbix file. In fact, the only modifications we are able to make, are report level measures, which we will be using later in this tutorial.


Below you can see how an inherited data model from Adventureworks database looks like in Power BI. Once again, we don't need to design and configure any relationships because it's all just a reflection of data model deployed to Analysis Services database.

AdventureWorks data model in Power BI

4. Let's create a few report level measures in this data model, which we will be later using to pass data directly from Power BI report to Power Apps application. We can do that by navigating to Report canvas, and either clicking on New measure button on the menu ribbon on top of our report or by right clicking on any table inside Fields list on the right side and selecting New measure option.

Creating New measure in Power BI report

5. DAX editor bar appears on top of our report where we can enter DAX expression to calculate our measures. We are going to create three new measures in total. Each of them will be simply calculating number of products that have a blank value in one of three corresponding attributes.


Products Without Size measure, shows how many products are missing Size attribute in our data model. +0 part at the end of the DAX expression makes sure that in case when there are no products without size attribute, our measure will show 0 value as a result instead of a blank value.

Products Without Weight measure:

Products Without Description measure:

DAX expression for calculated measure

6. Feel free to customize your report in a way that you like but make sure to include results of all three previously created measures somewhere in a report canvas. It will help us later to track and observe data transition from Power BI report to Power Apps application.


An example of such report is displayed below:

Power BI report canvas

Connecting to Power Apps visual


Now we can add a Power Apps visual to a report canvas and populate it with the fields from our data model in Power BI.


1. Select a Power Apps visual from Visualizations pane as shown below. Alternatively, you can navigate to Insert tab on the top of your report and click on Power Apps button.

Visualizations pane in Power BI

2. Let's drag three previously created calculated measures and Product Category Name field into PowerApps Data area of Power Apps visual.

Assigning fields to PowerApps visual

3. Once we drop fields inside PowerApps Data area, Power Apps visual prompts us with options to either Choose an existing application or create a new one from scratch. Let's chose the later one.


4. Power Apps studio is opened for us in a new web browser tab. By default it creates as a blank application with only a single Gallery object added to a canvas area. We can delete this object from our application and leave an empty screen.


Notice the PowerBIIntegration object under the Tree view of your application. This is a special object in Power Apps that allows a real time data transfer from Power BI report to Power Apps application.


Preparation of data storage to keep records of user input


Power Apps offers a vast number of different data connectors. For our solution we are going to need a data repository place where we could store data input from Power BI consumers and then retrieve it back to browse in Power BI report.


In this example we will be using Azure SQL database as a data storage for our data. Since both Power Apps and Azure SQL database are cloud based solutions, they are both able establish a data connection without additional tools. However, in case if you want to use on-premises SQL Server database as your data storage, then you will have to configure and set up a data gateway to establish a connection from Power Apps. Find more information about configuring on-premises data gateway.


Below you can find a T-SQL script that we are going to use to create a table for storing our data. Keep in mind, that in order for Power Apps to be able to properly manage records in our SQL table, we must create a primary key column in our table. This primary key will allow Power Apps application to uniquely identify and manage records.

  • Comments column will be used for storing manually entered text input from Power BI users

  • ProductCategory column will store product category value selected in our report

  • ProductsWithoutSize column is going to store value from a calculated measure

  • ProductsWithoutWeight column is going to store value from a calculated measure

  • ProductsWithoutDescription column is going to store value from a calculated measure

  • UserName will be used for saving full name of Power BI consumer

  • CreatedDate column will keep a record of date and time of user input


Configuration of Power Apps application


Once we have a table in a SQL database, we can continue by connecting it to our Power Apps application.


1. Select Data Sources option from the menu pane on the left side of your application.

2. Expand Connectors drop down list and choose SQL Server. If you previously connected from Power Apps to your SQL Server then existing connection will be available for you to select. Otherwise, go ahead with establishing a new connection by providing SQL Server and database names in the window that follows after.

SQL Server data connector

3. After successfully establishing a connection to a SQL Server, we will be asked to select a SQL table. Please choose the one that you created in the previous section of this post.


4. When finished, a newly connect SQL table will be available under In your app section in the Data sources menu.

SQL Server table

5. For visualizing data from Power BI and then sending it to SQL Server, we are going to use a Form object inside Power Apps.

Adding a new form to Power Apps canvas

6. Let's configure relevant properties of a newly created Form object.

  • Data Source: select SQL table that you connected to in the previous section

  • Edit fields: include all the fields from the SQL table except ID field

  • Default mode: change it from Edit to New. This option will make sure that all submitted form records will be inserted into our SQL table

Configuring properties of a form object

7. Our Form object has to be populated with correct values. Some fields should be taking data directly from Power BI report and others will be showing results from several Power Apps functions.


Let's set default values of form fields as stated below.


  • Created Date default value

Now()
  • User Name default value

User().FullName
  • Product category default value

First([@PowerBIIntegration].Data).'Product Category Name'
  • Products without description default value

First([@PowerBIIntegration].Data).'Products Without Description' + 0
  • Products without size default value

First([@PowerBIIntegration].Data).'Products Without Size' + 0
  • Products without weight default value

First([@PowerBIIntegration].Data).'Products Without Weight' + 0

First function allows us to retrieve only a single row from PowerBIIntegration object. By default, PowerBIIntegration returns a set of rows from Power BI report but since our report is designed in a way that it always returns only a single row, we should use First function to ensure that our Form object in Power Apps is able to properly process incoming values.

We can also make CreatedDate field invisible, as users don't need to modify this field and it will always have a value of a current date and time.


Assigning default values to the form fields

8. To further enhance Power BI users' experience, we are going to create a Success screen in Power Apps that will be displayed each time after submitting user input. Under Insert tab, click on the New screen drop down list and select Success.



9. Add a Timer object to a newly created Success screen. It can be found under Insert tab, Input drop down list.


10. Change properties of a Timer object.

  • Duration - set to 2000 ms

  • Auto start - enabled

  • Visible - disabled

  • OnTimerEnd property changed to the following command:

Navigate(scrComments)

All above property changes will make sure that Success screen is always visible for 2 seconds before disappearing and navigating back to the main screen of our application.

Properties of a Timer object

11. Back in the main screen, where we created our Form object, let's add a Button and configure it to save all values to a SQL Server and display a Success screen.


OnSelect method of a newly added button:

SubmitForm(frmNewRecord); NewForm(frmNewRecord); Navigate(scrSuccess)
  • SubmitForm method takes existing values from our form and sends it to a SQL server as an insert statement to the underlying table

  • NewForm resets form values provided by Power BI user. In our example it's only Comment value.

  • Navigate displays a Success screen after finishing previous two methods


Below you can see one way of how a form and a button in Power Apps can look like. An important part here is three fields that receives its values directly from three calculated measures which we previously created in our Power BI report.


To improve embedding appearance of Power Apps application inside Power BI report, let's go to the Settings of our application, select Advanced settings and enable Optimize embedding appearance option under Experimental features section. This change will ensure that our application is properly embedded to Power BI report without displaying an unnecessary background color.

Optimize embedding appearance

12. Finally, we can save and publish our application. Once done, PowerApps visual inside our report will be automatically updated with the latest version of application.


Below you can observe how we are interacting with our Power BI report and at the same time submitting data inputs using PowerApps visual.

PowerApps visual embedded to Power BI report

After submitting several comments, we can check and validate results in our SQL Server table.


Retrieve and display history of user input


Obviously, simply allowing users to record data isn't the most useful feature by itself. In order to get most value out of this solution, we are going to implement an option to review previously recorded data values and user comments. This would allow Power BI consumers to understand and have a better picture of historical business performance.

1. Let's go back to our Power Apps application. Inside a main comments screen, from the Insert tab, select a new vertical Gallery and place it just below a Submit button.


2. Change its Data source property to SQL Server table where we store our comments.


3. Change gallery's Items property to the following code:

Sort(
    Filter(
        '[PowerBI].[DataQuality]',
        ProductCategory = First([@PowerBIIntegration].Data).'Product Category Name'
    ),
    CreatedDate,
    Descending
)

This code ensures that SQL Server returns only records that have same product category as filtered in our Power BI report. Additionally, it sorts all retrieved records by created date in descending order.


4. Change Layout property of a gallery object to Title and Subtitle.


5. From the Fields property choose CreatedDate as Subtitle and UserName as Title.


If you previously provided multiple data entries under the same product category then your comments screen in Power Apps might look something similar to the following screen:


At this stage, we are only showing user name who recorded a comment and a date value of that action. It would make much more sense to enable our users to check specific details of every historical comment that was recorded in the past.


6. Create new blank screen in your Power Apps application. Navigate to the Insert menu, and pick Display form from the Forms drop down list.


7. Change its Data source property to a table from your SQL Server database which stores data entries from Power BI users.


8. Click on the Edit fields property and add all available fields except ID field.


9. Change form's Item property to the following command:

glrComments.Selected

The above command makes sure that our form object shows data of the record that users select in the comments gallery in the main screen.


10. Adjust your display form in a way that it would be best presented to the end users.


11. Add a Back icon or a button which will be used to return back to the main screen of our application.


12. Set its OnSelect property to the following command:

Navigate(scrComments)

Your History review screen might look something like below:


13. Back in the Comments screen, select the Right arrow icon inside historical comments gallery and change its OnSelect property to the following commands:

Select(Parent); Navigate(scrHistory);

Finally, we can save and publish our application. Go back to Power BI Desktop and test it by reviewing previously recorded values.

Same functionality can be expected when your report is published to Power BI Service. However, when sharing your report with other users, remember to share Power Apps application with same users as well, otherwise PowerApps visual won't work for them.



In this post, we covered a technique of embedding PowerApps visual to Power BI report while using Live connection to fetch data from tabular model. This approach allows your Power BI users to interact with report or dashboard and take actions in real time within same place.


For more information about limitations of PowerApps visual for Power BI you can go to official documentation.

855 views

Address

Winghouse

Ørestads Boulevard 73

Copenhagen, 2300

Denmark​​

Contact

+45 91101130

+45 60907466 ​

info@nordone.dk

© 2020 by NordOne, Copenhagen Denmark