Adding Comments & Sending Emails in Power BI using PowerApps

January 9, 2018
Share this...
Tweet about this on TwitterShare on LinkedInShare on FacebookShare on Google+Pin on PinterestShare on Reddit

A couple of days ago, a new custom visual was released for Power BI – PowerApps (in preview mode). As mentioned in the team blog, there are a ton of use cases–

you will be able to pass context aware data to a PowerApps app which updates in real time as you make changes to your report. Now, your users can derive business insights and take actions from right within their Power BI reports and dashboards. No need to switch tabs to open the separate apps, copy paste data from one window to another or worry about fat fingering the wrong customer id or invoice amount.

If you think about it, this is a game changer – you finally have a BI tool that allows you to collaborate and take actions right within the report. How many times have you looked at a report, found out an insight and wished that you could send an email to the account manager, only to forget later? Well, now you don’t have to worry about that, as I am going to show you an example of how we can collaborate by adding comments within the report (not just comments, but context aware comments, based on what you are selecting) as well as show how to send emails (to the appropriate people based on your selection).

 

Note: This is a long post, as PowerApps might be new to many BI folks and I wanted to include as many images as possible. If you just want to see the end result, please scroll to the end.

 

Adding comments and sending emails in Power BI using PowerApps

 

Steps

1) For this demo, I am using a simple Power BI report that shows the stock volume for three companies by date and their account managers. I made a simple line chart along with a slicer for the companies in Power BI Desktop, as shown below.

 

image

 

2) Deploy the report to Power BI service, and then edit the report. Add the PowerApps custom visual from the store.

 

AddPowerApps

 

3) Now click on the PowerApps custom visual, drag and drop the Account Manager and Stock into the PowerApps data field, and click on Create New. 

 

CreatePowerApps

 

4) The intention of the PowerApps visual is to provide an input area for the comment as well as to send emails to the account managers of the respective companies. For storing the comments, I am using a simple table in an Excel file and the format of the table is given below

 

image

 

Note that we have a column for the stock, comments, date of entry and the user who entered the comment. Now, go ahead and create 3 new data sources – Office365 Outlook, Office365Users and the Table in the excel file.

 

image

 

5) At this point, I started editing the comments page (which is the first page) and the final result is given below.

 

image

 

Some of the key changes that I did are:-

  • Changed the Data field in the Gallery to filter for only those fields in the Table1 (comments table in excel) which are filtered in Power BI.

 image

  • Changed the Gallery layout to display the Stock, User, Comments, Date from the Excel file as well as the photo of the user. You can get the photo of the user by using the formula given below

 Office365Users.UserPhoto(Office365Users.UserProfile(ThisItem.User).Id)

 

 image

  • Changed the background color of the Gallery to blue, and also added 3 buttons – Comments, Email and the plus icon. The Comments and Email button are given appropriate colors so that it looks like a tabbed structure. Also, their OnSelect properties are set to navigate to Email page and AddComments page on selecting.
    • Comments – No formula.
    • Email – OnSelect property is set to Navigate(Email, Fade)
    • Plus Icon – OnSelect property is set to Navigate(AddComment, Fade); ResetForm(EditForm1)

 

6)  The next page is the Email page and is used when you want to send an email to the account manager without adding a comment. For eg, maybe you want to send a confidential email to the account manager about an insight or question.

 

SendEmailTab

 

Some of the key changes that I did are:-

  • Created a form for Table1 and displayed the stock & comments. Also added the User and Date fields, but hid them (by setting the DisplayMode property to Parent.DisplayMode.Disabled) after giving a default values
    • User – Default property set to User().Email
    • Date – Default property set to Today()
  • Created a button for Send Email, which will send the message as an email. Set the OnSelect property to the formula given below

Office365.SendEmail(“jason143@gmail.com”, “Comments for ” & DataCardValue4_1.Selected.Value & ” from ” & User().FullName, DataCardValue2_1.Text); ResetForm(EditForm1_1); Navigate(Comments, Fade)

  • Added the Default value of the Stock to the formula below, so that it will default to the value selected in Power BI

LookUp(Table1,Stock=First(PowerBIIntegration.Data).Stock).Stock

 

7) The next page is the AddComment page. This is the page that you navigate to when you clicked on the Plus Icon in the Comments page and is used for adding comments.

 

AddComments

 

Some of the key changes that I did here are:-

  • Similar EditForm as the Email page – displaying only Stock and Comment and hiding User and Date
  • A toggle that determines whether you want to send an email along with adding the comment or just add the comment only.
  • 2 icons on the top
    • one for canceling the form – OnSelect property set to Navigate(Comments, Fade); ResetForm(EditForm1)
    • one for submitting the form. OnSelect property is set to

If(Toggle1.Value=true,Office365.SendEmail(“jason143@gmail.com”, “Comments for ” & DataCardValue4.Selected.Value & ” from ” & User().FullName, DataCardValue2.Text)); SubmitForm(EditForm1); Navigate(Comments, Fade)

 

8) Now you can save and publish the app. See the results in the Power BI page. I also added a toggle using bookmarks in the Power BI page to show/hide the comments / PowerApp visual.

  • Show contextual comments

 

PBI Show contextual comments

 

  • Add comments based on what you have selected in Power BI. Also note that an email has been sent to the appropriate account manager with the comment.

PBI Add comments

 

  • Send an email to the account manager directly (without posting the comments)

PBI Send Email

 

I am pretty sure you guys are going to find a lot more use for this custom visual. Note that this is currently in Preview, so it is likely that you will hit upon some bugs / issues, but those should be gone when it is released.

 

 

Posted by SQLJason

5 comments

Fred Kaffenberger

Fantastic post, Jason! I spent half the day yesterday trying to figure out how to add Power App that would enable me to collect feedback from my report readers, so I think this gets me another step along that process.

Great Example, where can i download this PBI file from, Thanks
Sohail

Not really sure how to share as you will not have access to the PowerApp that I made. Let me know if you have any questions when you follow the steps and I will try to help as much as I can.

Great Piece of Work..! I am very new to power Apps…it will be good if you can share the complete step by step video for reference in future.

Many thanks

Leave a Reply