Power BI Comments Form with Power Apps


By:   |   Updated: 2021-06-01   |   Comments   |   Related: > Power Apps


Problem

When you use Power BI to visualize datasets, you may want to provide your users the option to leave comments either for the whole report, for a certain table or even for a specific data point.

Solution

To implement this scenario, you can embed a Power Apps canvas app into a Power BI report. This tip will show you how.

SQL Server Database Setup

Let us first examine the example setup. You can follow these steps as a training exercise or read through the whole tip first and then adapt it top your needs. For this example, I am using the AdventureWorks LT 2019 database. You can download it from here. To ensure we have a place to store our comments, we must create a new table in the database:

CREATE TABLE SalesLT.Comments (
    CommentID INT IDENTITY(1,1) PRIMARY KEY,
    ProductID INT,
    Comment VARCHAR(2000),
    Author VARCHAR(255),
    CommentTimestamp DATETIME,
    FOREIGN KEY (ProductID) REFERENCES SalesLT.Product(ProductID)
);

For this new table, at the very minimum we must have an ID column and columns for the comment text, author name and date / time the comment was made. In this case we will be leaving comments for products from the SalesLT.Product table so naturally we also need a foreign key on ProductID. In this way we can relate every product to one or many comments later.

Power BI Report

Open Power BI desktop. From the blank canvas, click Import data from SQL Server. The configuration should look like what is on this screenshot (if using localhost):

Connect to SQL database from PowerBI

I am using DirectQuery, but the steps here apply for Import as well. Next, in the navigator, import only SalesLT.Comments, SalesLT.Product and SalesLT.ProductCategory:

Power query navigator in PowerBI

AAfter that add some visuals to the one page in the report. The field from the dataset that we want to use in the comments app may or may not be present in a visual. So, I have made this page that displays average category prices (1), a table with products (2), the comments table (3), and a slicer for category and product (4). After that, use the Power Apps for Power BI icon and add the Power Apps placeholder (5) to the page too:

Example report layout

Next, select the Power Apps visual you just added and drag the Comment field from the SalesLT.Comments table:

Power Apps in PowerBI configuraton

Here it is important to differentiate between Create new and Choose app. Create new will ensure you have the PowerBIIntegration control in your canvas app allowing you to access the table from the dataset without explicitly making a connection to it. On the other hand, if you pick Choose App, a list of all your apps will appear along with a dropdown for environment selection allowing you to embed apps from various environments in your tenant. In short, with Choose App you may embed not only a comments app, but any other app that makes sense to have inside the report. Additionally, there is no limit on how many apps can be present on a page in Power BI, but you have to be mindful of the impact on the loading time. At this point we are ready to head to Power Apps and make the comments app.

Power Apps Comments App

Click Create new (unless you have an existing app), then click Go to PowerApps studio. A pop up may appear warning you that you are navigating away, so agree and continue. This will result in the Power Apps studio opening in your browser with a blank app in the default environment. By default, you will see a Gallery control added and the notable PowerBIIntegration control:

PowerBIIntegration control in Power Apps

Although you can use this control to populate a gallery or a text field, or even to join to local collections, you cannot use it to write back to your dataset. So, the first order of business is to also make an explicit connection to the database and the Comments table. Do this by going to the datasources tab, click Add data and pick the SQL connector. You may have to configure a connection prior to doing this or by clicking Add a connection. In this case, I have a preconfigured connection to my localhost. When you pick a connection to a database, Power Apps will also prompt you to choose at least one table from the target DB, so pick SalesLT.Comments and SalesLT.Product. Be advised at this point you are using a premium connector that will require an additional per-app or per-user license in case your target users do not have it yet.

SQL Server connection in Power Apps

Now apply the following configurations:

Control - Drop down

  • Property - Items
  • Explanation - This setting will populate the dropdown automatically with values from the Product table. Beware dropdowns are limited to 500 items. In future tips we will explore how to circumvent that.
  • Formula - 'SalesLT.Product'.ProductID

Control - Text Input

  • Property -EnableSpellCheck
  • Explanation -These two settings ensure a good user experience while writing the comment inside the box. If you keep the default Mode setting (Single line), there will be no scrollbar for longer comments.
  • Formula - true
  • Property - Mode
  • Formula - Multiline

Control - Button

  • Property - OnSelect
  • Explanation - By using the Patch formula, we write the comment back to the database. For the Author and Timestamp values we use the built-in functions accordingly. With the Refresh function we ensure the data source will be refreshed and the comment will appear immediately in the gallery. The Reset function will reset the comment input.
Patch(
    'SalesLT.Comments',
    Defaults('SalesLT.Comments'),
    {
        ProductID: ProductIdSelector.Selected.ProductID,
        Author: User().FullName,
        CommentTimestamp: Now(),
        Comment: CommentBox.Text
    }
);
Refresh('SalesLT.Comments'); Reset(CommentBox)

Control - Gallery

  • Property - Items
  • Explanation - The gallery was added automatically when the app was created. However, the Items property must refer to the actual data source and not the PowerBIIintegration control
  • Formula - 'SalesLT.Comments'

Note the gallery has an embedded object called Template. It allows you to configure once every row that will be created in the Power Apps interface for every comment coming from the database. You may consider adding some color, border colors or other slight visual improvements. By adding more labels into the Template, you can show other relevant attributes of the current item, such as the author and the timestamp. The following screenshot shows an overview of all configurations. At this point the overall look of the app should resemble something like this:

App preview with gallery template settings

As soon as you save and publish the app in the Power Apps studio, the Power Apps visual in Power BI will refresh automatically and visualize the newly made app in the report.

The canvas app live in the PowerBI report

Note that the comment will not appear immediately in the comments table during report design time or runtime. The comment you added will only appear once the dataset itself has been refreshed according to the refresh configuration in the Power BI service. However, the gallery in the Power App ensures that comments are shown immediately.

Enhancement – Option to edit a Comment

Let's look at how to enable the authors to edit their comments. First, substitute the label control for the comment text with a text input control. From the icons’ menu, add three icons: a pen icon for editing, a cross icon for canceling and a check icon for confirming. Assign the following configurations:

Control - Screen1

  • Property - OnVisible
  • Explanation - We are setting a helper Boolean variable to track the state of a comment being edited.
  • Formula
Set(
    editMode,
    false
)

Control - Pen icon

  • Property - Visible
  • Explanation - Ensure the icon is displayed only for the current userís comments.
  • Formula
If(
    ThisItem.Author <> User().FullName,
    false,
    true
)
  • Property - OnSelect
  • Explanation - Change the value of the state tracking variable to whatever is the opposite, e.g. if currently true, set to false.
  • Formula
Set(
    editMode,
    !editMode
)

Control - Text input

  • Property - Default
  • Formula
ThisItem.Comment
  • Property - DisplayMode
  • Explanation - Note all text input fields will go in Edit mode in the whole gallery, However the cross and check icons will be available only for the currently selected item.
  • Formula
If(
    editMode,
    DisplayMode.Edit,
    DisplayMode.View
)
  • Property - Fill
  • Explanation - Highlight the background of the text box.
  • Formula
If(
    !editMode,
    RGBA(
        220,
        220,
        220,
        1
    ),
    RGBA(
        255,
        255,
        255,
        1
    )
)

Control - Check Icon

  • Property - Visible
  • Explanation - If the state is set to edit and the ID of the current item equals the ID of the selected item in the gallery, show the submit icon.
  • Formula
If(
    And(
        editMode,
        ThisItem.CommentID = Comments.Selected.CommentID
    ),
    true,
    false
)
  • Property - OnSelect
  • Explanation - Clicking the icon will save the change by overwriting the specific comment in the database. The LookUp function ensures we write back to that comment.
  • Formula
Patch(
    'SalesLT.Comments',
    LookUp(
        'SalesLT.Comments',
        CommentID = Comments.Selected.CommentID
    ),
    {Comment: CommentText.Text}
)

Control - Cancel Icon

  • Property - Visible
  • Explanation - If the state is set to edit and the ID of the current item equals the ID of the selected item in the gallery, show the cancel icon.
  • Formula
If(
    And(
        editMode,
        ThisItem.CommentID = Comments.Selected.CommentID
    ),
    true,
    false
)
  • Property - OnSelect
  • Explanation - Clicking the cross (cancel) icon will remove the edits and reverse the state of the editMode variable.
  • Formula
Reset(CommentText);
Set(
    editMode,
    !editMode
)

Here is how the app should look after these configurations:

On the left it is "in edit mode" and on the right "not in edit mode".

app with edit mode off app with edit mode on

Now you can save and publish this app and it will be available in your Power BI report providing the capability to add end edit comments.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Hristo Hristov Hristo Hristov is a Microsoft certified data professional, specializing in Power Apps and Power BI.

View all my tips


Article Last Updated: 2021-06-01

Comments For This Article





download














get free sql tips
agree to terms