PowerApps

So this happened yesterday – one of my customers pinged me and asked whether it is possible to set customized data alerts for her end users? I froze for a second, knowing that such a functionality is not available out of the box but knowing how flexible Power BI is, I decided to explore her use case further. Worst case, I know I have the backing of the world’s best product team, and could submit a request to build this for us. Basically, she wanted her end users to get data alerts if specific products got sold in the last 24 hours (which should have been easy with the regular data alerts functionality in Power BI), but the challenge was that she wanted her users to set (add/delete) their own products. As I said earlier, this functionality is not available out of the box but with the PowerApps custom visual for Power BI and some DAX, we can definitely create a workaround.

 

 

As with any of my PowerApps post, this is going to be long, so scroll to the bottom if you just want to see the end result.

 

Source

The data source that we will be using for this post is a simple excel file stored in One Drive for Business. The excel table gives the sales by company and date. The requirement is that the end users should get data alerts if any sales occurred in the previous day for any of the companies that they chose.

 

Solution Summary

To achieve the end result, we will create a table that maps the users to the companies, and then create a DAX measure in Power BI that shows the Sales if the conditions are met. The mapping of the companies to the users can be entered directly by the end users using a PowerApps custom visual within Power BI. The DAX measure can then be pinned to a dashboard and the alerts can be set on top of it. Also, Row Level Security has to be applied on the mapping table to ensure that the End user sees only his selections.

 

Solution

Follow the steps below:-

1) Create a user mapping table in an Excel file and store it in One Drive for Business (I am using Excel file stored in One Drive, but this could be any of the data source that PowerApps can read/write from and Power BI can read from). For now, I am adding 2 users – Admin and AdeleV and their selected companies for which they would like the data alerts.

 

 

The model would look like below. Note that the UserMapping table is a disconnected table.

 

 

2) Create a measure that satisfies the 2 requirements – filter selected companies for the user and sales from yesterday.

 

3) Create a new role and set RLS for the UserMapping table. The intention is to filter the UserMapping table to the current user who has logged in and that can be done with the expression below

 

4) Create a simple report with the SalesAlert measure that we created in Step 2. Note that I am also adding the User name, UserMapping table as well as the Sales table so that you can see what is happening when we are logging in as different users. Right now, I am still logged in as Admin.

 

 

Pin the SalesAlert card to the dashboard (along with the Username card, so that you can see who is logging in). I also created a simple data alert that sends an email if the threshold is above 0. The dashboard is shown below

 

5) Go back to the Power BI report, edit it in the Power BI Service so that you can create a PowerApps custom visual. For this demo, I am passing the UserID and Company in Power BI.

 

 

6) Then, I created an app with 3 screens and the 2 data sources used are the UserMapping table in Excel as well as the Sales table in Excel. Let us look at the Home screen first, where we will display all the companies that are selected for the appropriate user.

 

 

The main highlights here are

• A plus icon to navigate to the AddScreen - OnSelect property set to Navigate(AddScreen, Fade)
• A dropdown to display the UserIDs from Power BI.
  • Items property set to Distinct(PowerBIIntegration.Data, UserID)
  • Default property set to First(PowerBIIntegration.Data).UserID
• A gallery to display the companies for the selected UserID
  • Items property set to Filter(Mapping, UserID in Dropdown1.Selected.Value)
  • NextArrow Icon to navigate to EditScreen – OnSelect property set to Navigate(EditScreen, Fade)

 

7) The next Screen is the EditScreen. This screen is used to delete an existing mapping.

 

 

The main highlights here are

• A X icon to cancel out from the screen – OnSelect property set to Navigate(HomeScreen, Fade); ResetForm(Form1)
• A trash can icon to delete the selected record – OnSelect property set to Remove(Mapping, CompanyGallery.Selected); Navigate(HomeScreen, Fade)
• An Edit form
  • Data property set to Mapping
  • Fields are Company and UserID
  • Item property set to CompanyGallery.Selected

 

8) The last screen is the AddScreen and is used to add any new mappings for the end user.

 

 

The highlights from this screen are

• Edit form to add the records
  • DataSource property set to Mapping and Item property set to Defaults(Mapping)
  • Company and UserID fields are selected
  • Company – Items property set to Distinct(Table1, Company) and Default property set to First(Table1).Company. This will display all the companies from the Sales table in the dropdown
  • UserID – Default property set to User().Email
• X mark to cancel out from the screen – OnSelect property set to Navigate(HomeScreen, Fade); ResetForm(EditForm3)
• Tick mark to add the entry – OnSelect property set to SubmitForm(EditForm3); Navigate(HomeScreen, Fade); ResetForm(EditForm3)

 

9) Once you save and publish the app, also make sure that you are sharing it with all of the end users. 

10) Save the Power BI report, and publish it as an app. Share the App with the end users (in my case, Adele and Alex).

11) Make sure that the end users can access the App and also, make sure that each one of them creates a data alert on the dashboard with the same condition (just like we did in Step 4). The image below shows the data alert for Adele.

 

 

12) Now the users should start getting alerts for the companies that they have selected. Based on our data alerts,

• Admin should get a data alert email if JPM, XOM or NVDA has a transaction for yesterday. Note that if you are an administrator, you will get the alert even if the data alerts for one of the end users’ condition is true. This is because the way we have setup the measure and Row Level Security, an administrator can see all the companies for all the users, and hence will get an alert if one of the selected companies has sales for yesterday (Jan 15).

 

• Adele should get a data alert email only if there is a transaction for NVDA (44 for Jan 15). In this case, she will get a data alert.

 

• Alex will not get a data alert as he has not selected any company.

 

13) Now login as Alex, and navigate to the report. Use the PowerApps custom visual that we created before to enter a new selection (in this case, JPM) for Alex. You can either wait for the dataset to be refreshed, or in my case, login as an admin and refresh the dataset manually. Once the dataset is refreshed, Alex can see the company that he selected in the mapping table, and can also see that the SalesAlert measure has been updated. 

 

14) At this point, Alex should also receive an email alert.

Summary

Even though customized data alerts are not possible out of the box in Power BI (so far, as of 1/16/2018), it is possible to use the PowerApps custom visual, and make customized data alerts for each of the end users as well as give them the ability to modify / add their selections for data alerts.

Related

Adding Comments & Sending Emails in Power BI using PowerApps

 

 

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.

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.

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

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.

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

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.

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

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.

• 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)

 

• 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.

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(“[email protected]”, “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.

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(“[email protected]”, “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

• 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.

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

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.