Using the Sentiment Analysis Action from PowerApps

 

AI Builder has some amazing features. This walk through will get you started with using their sentiment analysis from PowerApps

  1. Login to PowerApps


  1. Navigate to Solutions

     


 

  1. Create a new Solution

  1. Open your Solution

  1. Add a new Flow

  1. Set the trigger to PowerApps. Note I also named the flow at this step: “Sentiment from PowerApps”

  1. Add the “Predict” action to the Flow.

    Note: if you don’t see the “Predict” action, you are likely NOT in a solution. NOTE this is required!!!!!!!!!!

  1. Set the model to “SentimentAnalysis Model”

    Note: the other AI Builder Models I had created and available to the Flow

  1. Insert the following Text into the Request Payload

    {“text”:”My Text”, “language”:”My Language”}

     

    This from https://docs.microsoft.com/en-us/ai-builder/flow-sentiment-analysisc (watch those evil smart quotes!)

     

 

 

  1. Replace the “My Text” argument with Ask in PowerApps by clicking the “Ask in PowerApps” shape in the bottom of the action

  1. Replace the “My Language” argument with Ask in PowerApps by clicking the “Ask in PowerApps” shape in the bottom of the action

 

  1. Add a new Step and add the Parse JSON action

  1. Specify the content as the Response Payload. Specify the schema as the JSON below.

JSON for the Schema:

 

 

{

“type”: “object”,

“properties”: {

“predictionOutput”: {

“type”: “object”,

“properties”: {

“result”: {

“type”: “object”,

“properties”: {

“sentiment”: {

“type”: “string”,

“title”: “documentSentiment”

},

“documentScores”: {

“type”: “object”,

“properties”: {

“positive”: {

“type”: “number”

},

“neutral”: {

“type”: “number”

},

“negative”: {

“type”: “number”

}

}

},

“sentences”: {

“type”: “array”,

“items”: {

“type”: “object”,

“properties”: {

“sentiment”: {

“type”: “string”

},

“sentenceScores”: {

“type”: “object”,

“properties”: {

“positive”: {

“type”: “number”

},

“neutral”: {

“type”: “number”

},

“negative”: {

“type”: “number”

}

}

},

“offset”: {

“type”: “integer”

},

“length”: {

“type”: “integer”

}

},

“required”: [

“sentiment”,

“sentenceScores”,

“offset”,

“length”

]

}

}

}

}

}

},

“operationStatus”: {

“type”: “string”

},

“error”: {}

}

}

  1. Add a PowerApps Response Action to the Flow

  1. Set the PowerApps Response to return an output a text value that is documentsentiment object from the parse JSON action.

  1. Save flow
  2. Go back to Solutions

  1. Add a new Canvas App. ( in this is a Phone form factor…but it isn’t really that important)

  1. Add a button and a Text Input and a button control

 

  1. Add a label control and set the text equal to mysentiment.sentiment

    PowerApps will complain about this…ignore for the time now.

  1. Select the button you added above and select the menu “Actions”, select Flows then select the Flow you created above.

NOTE: This is currently not working as Flows can not be referenced from a PowerApps in a Solution.

It is working in our staging environment and should be working soon! (Where the screen shots were taken)

  1. And here it is running!

Note the AI action gives many sentiment heuristics such as the scores for each sentiment type

AI Builder Object detection Hands On Lab

 

Download the latest version of this lab here:

https://github.com/microsoft/PowerApps-Samples/blob/master/ai-builder/labs/AIBuilder_Lab.zip

 

 

Object detection

Object detection lets you count, locate, and identify selected objects within any image. You can use this model in PowerApps to extract information from pictures you take with the camera, or load into an app.

In this lab, we will build and train a detection model and build an app that uses the detection model to identify objects from available images.

To get started with AI Builder

Go to PowerApps.com and sign in

 

Go to solutions and import the AI_Builder Sample

 

Navigate to AI Builder

 

 

Note: If you are building the first model in an environment, click on Explore Templates to get started.

 

 

Exercise 1

In this exercise we will build and train the Object Detection model for three varieties of tea.

  1. In PowerApps maker, expand AI Builder and select Build. Select Object Detection.

 

  1. Name your model Green Tea Product Detection and because you are working in a shared environment also make sure to include your name as part of the model name. This will make it easier to find later. Click create.

  1. Your screen should now look like the image here.

  1. Notice the progress indicator on the left. Those are the steps we will follow now to build and train our model.

  1. We are now going to define the objects we are tracking. Click on the Select object names.

 

  1. From the entity list, select Object Detection Product.

 

  1. Select the Name field and click Select field.

 

NOTE: As the solution import didn’t bring in this data you can enter it in via Data

  1. Select the tea items and click Next.

 

  1. Notice the progress indicator has moved forward to the Add images step.

  1. Click add images.

  1. Select images from the set provided. You will need enough images to provide 15 samples for each type of tea we are tracking.

  1. Approve the upload of images. Click Upload images. After the upload completes, click Close.

 

  1. Click next to begin tagging the images.

  1. Select the first image to begin tagging.

  1. Hover over the image, near an item you wish to tag. A dotted-lined box should appear around the item. It has been detected as a single item that can be tagged.

  1. Click on the item and select the matching object name.

  1. If the pre-defined selector is not accurate, as in the below example, you can drag the container to draw it to accurately tag the item.

  1. Do this for each item in the image and for each image in your set. When you have tagged all of the images you uploaded click Done Tagging in the top right of the screen.

  1. Once you have completed tagging, you will get a summary of the tags. If you haven’t tagged enough for analysis, you will need to load and tag more examples.

 

  1. Once you have defined enough tags for training the model, you will be allowed to initiate the training. Click Next.

  1. Click Train.

  1. The training takes a few moments.

  1. Navigate to the saved model view and confirm your model has completed training.

  1. Select the model you just made.

  1. Select Quick test.

 

  1. Upload or drag and drop one of your test images to be analyzed.

     

  2. You will see the analysis and level of confidence for the match.

 

  1. Upload an image you know will not match. You will see the analysis and level of confidence for the match.

  1. Click close.
  2. Publish your model.

 

Exercise 2

We will now create a canvas app you can use for detecting the items that have been trained in our model. The product will be detected from the image and you will be able to adjust on-hand inventory for the item.

  1. Navigate to Apps, and select Create an app, then select Canvas. If asked, grant permission for the app to use your active CDS credentials.

  1. Select Blank app with Phone layout.

  1. On the maker canvas, select the Insert tab in the ribbon and expand AI Builder. Select Object detector to place this control on your app.

  1. Select the AI model you built.

  1. Resize the control to better use the space.

  1. Make sure to leave room for more items we will be placing soon.

  1. Play your app.

  1. Click on Detect.

  1. Choose one of your test images and click Open.

  1. The image will now be analyzed.

  1. Our model has detected each tea in the image.

  1. Exit the app player.

 

Bonus exercise- build out the data in your canvas app

 

  1. We will now select our data source. Select View from the ribbon and select Data Sources.

  1. Click + Add Data Source.

  1. Add the Common Data Service data source. Do not use Common Data Service (current environment).

  1. Select the Object Detection Products entity and click Connect.

  1. Close the Data pane.
  2. With Screen1 selected in the Tree view, navigate to the Insert ribbon tab, expand Gallery and select Blank vertical gallery.

  1. Rename the Gallery productGallery. You are re-naming the gallery so you can reference it from your formulas.

  1. Resize and move the gallery control to fit the available space on the screen, leaving some space at the bottom for using later.

  1. Select the edit icon from the gallery.

  1. Add a label to the gallery.

  1. Click edit again and add a Text input box to the gallery. Resize and place it to line up with the label we’ve already placed. We will be updating inventory counts in this text box.

  1. Rename the Text Input inventoryInput. You are renaming this control so you can reference it from your formulas.

  1. With focus on the Screen1 in the Tree view, click in the ribbon on Insert and select Button.

  1. Drag and move the button to the bottom of the screen, double click on it to edit the text. Rename it to Update.

  1. We will now add the user message to give the user confirmation their submission was accepted; we will define this logic later. With focus on Screen1, insert a label, drag it to the bottom of the screen.

 

  1. We will now add logic to the controls we’ve placed on the screen. Select the Gallery and replace the Items formula with the following.

    ‘Object Detection Products’

  1. Select the label in your gallery. Replace the Text formula with the following:

    ThisItem.Name

  1. Select inventoryInput and replace the formula for Default with the following:

    LookUp(‘Object Detection Products’,Name = ThisItem.Name).’Inventory Total’

  1. Select the other label (the one that shows at the bottom of the screen) and replace its text with the following:

    usermessage

  1. You’ll notice that area now looks blank. We will configure that message in our next step.

  1. Select the button control and replace the OnSelect with the following:

    ForAll(productGallery.AllItems,Patch(‘Object Detection Products’,LookUp(‘Object Detection Products’,Name=DisplayName),{‘Inventory Total’:Value(inventoryInput.Text)}));Set(usermessage,”Updated ” & CountRows(productGallery.AllItems) & ” items”)

  1. Play the app again.
  2. Click Detect.

  1. Select an image to evaluate.

  1. Update the quantity for the correct product and click Update.

  1. The bottom should show a message now.

 

 

 

Creating dynamic Power BI tiles inside of PowerApps

Related demo can be found here: 

https://powerusers.microsoft.com/t5/PowerApps-Community-Blog/Integrating-PowerApps-Power-BI-and-Flow…

 
 

The second walk through below: 

PowerApps is the perfect complement to Power BI in that it makes it very easy to update or add data to the underlying datasets that Power BI is Visualizing. 

This walk through is using the Power BI sample data.

Step 1. Create a Dashboard using the Power BI Samples by selecting “Get Data” > Samples


2. Select the Sample “Retail Analysis”


Step 3.  Create a Canvas based PowerApp


Step 4. Insert a Power BI Control

From the insert ribbon choose controls and scroll to the Power BI Tile


Step 5. Setup the Power BI Control


 

 

Step 6.  Add some buttons to set the filter context

On the Insert ribbon select controls and add a couple (~three) buttons. 


 

Step 7. Copy the TILE URL Text

 


 

Step 8.  Create and set a variable to set our visual ….and our filter context

In the OnSelect of each button set a variable to the tile URL property

 


 

 

Step 9. Set the TileURL property of the Power BI Control

Select the Power BI control and scroll to the TileURL property then set the property to MyVar

 
 


 

Step 10 Copy this Text to the second button’s on select and add the text:

&$filter=Store/Territory eq ‘NC’

To the end

 

Step 11 Test the application

 

PowerApps to update and refresh data in Power BI Hands On Lab

 

Walking through how to refresh datasets and create near real time reports in Power BI with the PowerApps Custom Visual.

Starting from Power BI we need a report that is based on Direct Query. To save time you can download this report from here:

https://community.powerbi.com/oxcrx34285/attachments/oxcrx34285/DataStoriesGallery/3027/2/PowerApps_with_refresh.pbix

 

Upload the report to Power BI.com

 

  1. Please log into Power BI https://PowerBI.com using the supplied accounts i.e.

PAUser1@powermvps.comPAUser108@powermvps.com

Pass@word1

To save some steps the directions will just show doing this from “My Workspace” ….please remember “my workspace” is not really a recommended location.

  1. Select “Get Data” and then Files


  1. Select Local File and upload the PowerApps_with_refresh.pbix file from step #1

 

https://community.powerbi.com/oxcrx34285/attachments/oxcrx34285/DataStoriesGallery/3027/2/PowerApps_with_refresh.pbix

 

 

  1. Update the security for the report connection back to the SQL Server by clicking on the three dots after PowerApps_with_refresh Dataset.

    IF you don’t see the Datasets click on “My Workspace” to expand out the artifacts in the workspace. …this may require a couple of attempts!

 

IF you don’t see the Datasets click on “My Workspace” to expand out the artifacts in the workspace. …this may require a couple of attempts!

 

  1. To update the connection credentials, click on dataset settings, click on the “PoewrApps_with_refresh dataset and then select “Edit Credentials”.

 

  1. To configure the credentials to use the

    basic authentication method with the Username:

    Ann and password provided.

    Pass@word1

  1. Test the report that it is working!

     

 

  1. Edit the report to add the PowerApps Custom Visual

     

 

  1. Add the PowerApps Custom Visual to the report

 

  1. Add the custom visual to the report canvas

  1. Add all the fields to the Visual and specify we are building a new application and note the environment

  1. In the new form wizard specify to add a form

  1. Delete the default Gallery as it won’t be used. (note it is already populated with data from Power BI)

 

  1. Specify a Data source for the PowerApps form.

17. Specify the data source is a SQL Server source

18. Specify to use a new connection

 

19. Create a new SQL Connection

Supply the following Data for the connection String

Server: tcp:powerplatformdemos.database.windows.net

User: Ann

Password: See front of class

 

 

 

 

 

20. Specify to use the “Orders” Table

 

21. Select the fields property for the form.

 

22. Specify to use all the fields

23. Add Two Buttons

24. Set the OnSelect property to:

SubmitForm(Form1) ; PowerBIIntegration.Refresh()

Also Set the Text property to “Submit”

25. Set the onselect property of the first button to:

NewForm(Form1)

 

26. Save your application File > Save > Save (yes two of them!)

27. Test your application!!!!!!!!!!!!!!!!!

Mid-Month PowerApps Video Round Up!

 

While we are only 11 days into the month, we already have an amazing selection of PowerApps videos for your viewing pleasure!

If you haven’t already checked out the Community Gallery be sure and do so!

 


 

Creating Update-able Power BI Reports with PowerApps

 

 


Using PowerApps Visual in Power BI to interact with data

 

 

 


Creating a PowerApps Menu Components

 

 


PowerApps Deep Linking How To

 

 


PowerApps With Function

 

 


Data Validation Demystified – IsMatch()

 

 


Getting Started with the PowerApps Component Framework

 

 


PowerApps In-App Security Trimming – Easy SharePoint Method

 

 


PowerApps Draggable/Moveable Control

 

 


Adding a Signature Line to PowerApps and Saving

 

 


Walk through creating the internal Microsoft PowerApps Tool “Thrive” application with Pat Dunn

 

 

 


Share PowerApps With Guest / External Users

 

 


Microsoft PowerApps – Pass string array to SQL Stored Procedure from PowerApps using Flow

 

 


Shoutouts Template Configuration – Save data in SharePoint

 

 


Connect XrmToolBox To CDS

 

 


PowerApps CDS Security


 

Creating Updateable Power BI Reports with PowerApps by Ike Ellis

When asked what content our blog readers and video viewers would like to see more of, the most requested topic is: “Industry Best Practices”.

With that in mind we will be starting a series of Blog posts and Webinars by our MVPs highlighting work they have done.

The first in this series is by Ike Ellis (see bottom of post for more about Ike) and in this post he highlights how you can create a solution that enables a mobile sales force to update their own Power BI reports.

Webinar on this topic: September 12th 10AM PST

Where: https://aka.ms/IkeEllisWebinar

Combining PowerApps with Power BI to create powerful and compelling solutions

By Ike Ellis

One of my customers is a very large RV dealer with multiple locations. They allow sales people to set their own monthly goals. They wanted a Power BI report that would update when sales came into the database and when salespeople updated their particular goals for each month. Creating an updateable report is a very common request from customers.

Each RV dealership is humongous. RVs are individually large, so when you’re selling a lot of them, the lots have to be pretty large to accommodate them. It is very difficult to cover a lot that size in WIFI. Additionally, sales people often engage with customers away from the dealership. This made Microsoft PowerApps an ideal solution for updating their goals and sales. PowerApps for mobile devices can use the mobile network, in addition to WIFI. PowerApps can use the PowerApps gateway to query and update records in databases that are behind firewalls.

Combining PowerApps with Power BI, along with all the Azure services, really makes each individual product so much more powerful and compelling to the customer. If we’re comparing Power BI with its competitors, emphasizing how easy it is to integrate with these other products is a major competitive advantage. After we showed a demo of the solution to the customer, it was an easy sell. There really is no competition.

This blog post is a simplified version of the app I created for the customer. It will show how a Salesperson can update their goal for the month, while decision makers can see the Power BI report update as the data refreshes.

For our demo, we’ll be using three Microsoft products together: Power BI, PowerApps, and Azure SQL Database.

Directions:
Sign up for Power BI, Power Apps, and a free trial of Microsoft Azure

 

Create and seed a new Microsoft Azure SQL Database.


  • Search for “SQL Database” and click on the first result


  • On the welcome screen, click “Create”.
  • Choose your subscription and location. Try to pick a datacenter that’s close to you. I chose “West US”, since I live in San Diego, California.
  • Type “SalesDB” as the name of the database.
  • Create a new server or choose an existing server.
    • Choose a name for the server. It needs to be globally unique. When you connect to the database from PowerApps and Power BI, you’ll use the full name, which will include the unique name you just entered with a prefix of “.database.windows.net.”
  • Click “Configure database”. This is where we will choose how much money we will spend on our database. For a real production application, we would obviously try to predict our load and size the database accordingly. For our demonstration, we can just use the cheapest solution possible. I chose a Basic tier database. I slid the slider bar for the amount of space I need down to 100MB. At the time of this writing, this database will cost about $5 per month. Not bad. Click “Apply”.


My filled out form looks like this:


  • Click “Review + create” and then “Create”.
  • One of the things that amazes me most about Azure is how quickly resources come online and are available to use. As a developer, I’ve put in requests with IT departments for my entire career. When I ask them to create a SQL Server with a new database for me, it usually takes between two weeks and two months. In Azure, it’s done in less than five minutes!
  • When it says the deployment is complete, click “Go to resource”.


  • Azure SQL DB has a built-in firewall. Three things need access to your new Azure SQL Database: your workstation, PowerApps, and Power BI. Click on “Set Server Firewall” to configure the firewall.


  • Make sure you click “Add Client IP” and verify that the button for “Allow access to Azure Services” is selected. Then click “Save”.


  • In the next few steps, we’re going to use Azure Data Studio to create a login account and password, assign permissions, create two tables, and seed those tables so that we can use your new Azure SQL Database.
  • On the connections window, click “New Connection”


  • Under Connection Details, type in your unique servername that you choose earlier. Remember it ends with “.database.windows.net”.
  • Under authentication type, click “Azure Active Directory – Universal with MFA Support”
  • Under account, click “Add New Account”.
  • This screen will direct you to go to a URL and enter in a code while helping you login to your Azure Account. This will authorize your Azure Data Studio to connect to your Azure SQL Server.


  • Under Database, type “SalesDB”.
  • Your Connection Details should look like this with the correct account selected:


  • Now in the connection window, right-click on your new server and click “New Query”
  • Paste the following code in the window and run it.

drop
table
if
exists SalesPeople;

drop
table
if
exists goals;

drop
table
if
exists Sales;

go

create
table SalesPeople

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, SalesPersonFullName varchar(100))

GO

insert
into SalesPeople

(SalesPersonFullName)

VALUES

(‘Sterling Sanchez’)

, (‘Rick Archer’)

, (‘Morty Figgis’)

, (‘Lana Smith’)

go

create
table Sales

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, CustomerName varchar(100)

, OrderDate datetime

, SalesPersonID int

, SaleAmount decimal(14,2))

go

create
table Goals

(SysID int
not
null
identity(1,1) PRIMARY
KEY

, SalesPersonID int

, GoalMonth int

, GoalYear int

, GoalAmount decimal(14,2))

go

insert
into Sales

(CustomerName, OrderDate, SalesPersonID, SaleAmount)

VALUES

(‘Arch Burgers’, ‘7/1/2019’, 1, ‘520.00’)

, (‘Bob”s Investigative Service’, ‘7/3/2019’, 1, ‘139.00’)

, (‘Arch Burgers’, ‘7/13/2019’, 1, ‘272.00’)

, (‘Southland Laundry’, ‘7/21/2019’, 1, ‘499.00’)

, (‘Smith Consulting’, ‘7/2/2019’, 2, ‘296.00’)

, (‘Reed Gardening Services’, ‘7/13/2019’, 2, ‘321.00’)

, (‘Fashion Design By Gilbert’, ‘7/25/2019’, 2, ‘673.00’)

, (‘Sterling Bar and Grille’, ‘7/30/2019’, 2, ‘152.00’)

, (‘Allington Investment Advisors’, ‘7/3/2019’, 3, ‘210.00’)

, (‘Rad Construction Supply’, ‘7/5/2019’, 3, ‘329.00’)

, (‘Sterling Bar and Grille’, ‘7/7/2019’, 3, ‘199.00’)

, (‘Allington Investment Advisors’, ‘7/17/2019’, 3, ‘195.00’)

, (‘Johnson Tool & Die’, ‘7/22/2019’, 3, ‘52.00’)

CREATE USER SalesUser WITH
PASSWORD
=
‘PowerAppsAreGreat!’

ALTER
ROLE db_datareader ADD MEMBER SalesUser;

ALTER
ROLE db_datawriter ADD MEMBER SalesUser;

  • Create a PowerApps application that allows SalesPeople to enter their goals for the month.


  • Name the app “Sales Goals” and select the phone layout. Click “Create”.


  • It will take a minute to create the new application.
  • You may need to skip a welcome screen.
  • We are going to connect this app to our Azure SQL Database. Click the “View” tab, then click “Data sources”. Type in “sql server” into the text box and click the red SQL Server icon.
  • Click “Add a Connection”.


  • Since our database is in Azure, we’ll click the radio button “Connect directly (cloud services)”.
    • Type in the SQL Server name from earlier that ends in “.database.windows.net”.
    • Type in SalesDB as the SQL database name.
    • Type SalesUser as the Username.
    • Type PowerAppsAreGreat! as the password.


  • For the table, we’ll select “Goals” and “Salespeople” and click “Connect”.
  • Our goal is to create to create a screen that looks like this:


  • We are going to create one data entry element at a time and then add the button. Then we’ll test our application.
  • Let’s start with the label at the top. On the Insert tab at the top bar, click “Label”. In the Text property, enter “ENTER GOAL AMOUNT FOR MONTH (0.00)”. Include the double quotes.


  • On the right side, you’ll see the PowerApps property window. You can use this window to control the behavior of an individual control in PowerApps.
  • Click on the Advanced tab on the window on the right.
  • In the search area, type “weight”.
  • Change the FontWeight to Bold


  • Change the Align to “center”.


  • We will be following that pattern with a lot of these properties without giving accompanying screenshots.
  • Now let’s add dropdown list for Goal Month.
    • Add a label to the control. Change the Text value to “Goal Month:”
    • Add a Drop Down control. On the left window. You’ll see a new dropdown titled DropDown1. Next to the name, you will see an icon with the ellipsis (…). Click on it and rename it to ddlGoalMonth. You will be renaming many of the controls. The completed Tree view on the left will look like this


Change the Items property to the following:

Table(
{
Text: “January”,
Val: 1
},
{
Text: “February”,
Val: 2
},
{
Text: “March”,
Val: 3
},
{
Text: “April”,
Val: 4
},
{
Text: “May”,
Val: 5
},
{
Text: “June”,
Val: 6
},
{
Text: “July”,
Val: 7
},
{
Text: “August”,
Val: 8
},
{
Text: “September”,
Val: 9
},
{
Text: “October”,
Val: 10
},
{
Text: “November”,
Val: 11
},
{
Text: “December”,
Val: 12
}
)

  • This makes the dropdown display the individual months in the correct order. It also assigns a value to the month so that it sorts correctly.
  • Now let’s create the dropdown for Goal Year.
    • Add a label and change the Text property to “Goal Year:”.
    • Add a drop down. Rename it to ddlGoalYear.
    • Change the Items property to [“2019”, “2018”]. In this case the value and the text are the same thing, so we don’t need to use a Table function in our code like we did for month.
  • Create the dropdown for Salesperson.
    • Add a label and change the Text property to “Salesperson:”.
    • Add a drop down. Rename it to ddlSalesperson.
    • Change the Items property to ‘[dbo].[SalesPeople]’.
    • Change the Value property to “SalesPersonFullName”.
  • Create the textbox for Goal Amount.
    • Add a label and change the Text property to “Goal Amount:”.
    • Add a text input control. Rename it to txtGoalAmount.
    • Clear out the Default property.
  • Add the Save button
    • Add a button control. Rename the control btnSave.
    • Change the Text property to “Save”.
    • Change the OnSelect property to the following code:

RemoveIf(
‘[dbo].[Goals]’,
SalesPersonID = ddlSalesperson.SelectedText.SysID,
GoalMonth = ddlGoalMonth.SelectedText.Val,
GoalYear = Value(ddlGoalYear.SelectedText.Value)
);
Collect(
‘[dbo].[Goals]’,
{
SalesPersonID: ddlSalesperson.SelectedText.SysID,
GoalMonth: ddlGoalMonth.SelectedText.Val,
GoalYear: Value(ddlGoalYear.SelectedText.Value),
GoalAmount: Value(txtGoalAmount.Text)
}
)

    • Each salesperson can only have a single goal for the same month and year. The REMOVEIF function finds a matching record on those three criteria and deletes the record. It then adds a new record based on the inputs
  • Let’s add a few goals and then check the database to see if the goals have updated.
    • Hit F5 to launch the application in your browser.
    • Add goals for each of the sales people for July 2019.
    • Make sure the goals are at least over $500, but below $3,000.
    • Click the save button.
    • Go back to Azure Data Studio. Open a New Query window for the SalesDB database. Enter “SELECT * FROM GOALS”. Execute that code. You should see records for goals for each of your sales reps.
  • There are a few improvements that we would make to this application before releasing it. I didn’t add these to this post because it would detract from the main solution.
    • When the user logs on to PowerApps, PowerApps knows the email address that the user used. This is the same email address that was configured when we shared the application to the user. Using this email address, we know who the user is and wouldn’t need them to select their own name in the dropdown.
    • Give the user the ability to see and search through all of their old goals.
    • Create an approval process where managers see the goal and approve it or deny it.
    • Create an audit log where we can see who entered what goal and when for accountability.
    • Create a status bar that provides feedback to the user so they know a record was successfully inserted.
  • Now let’s make the Power BI report that the decision makers will see.
    • Open Power BI Desktop
    • Let’s import all the data we need
      • Click “Get Data”
      • Choose “SQL Server”
      • For the server name, put the same server name you entered into PowerApps.
      • Enter “SalesDB” as the database.


      • Click OK.
      • On the next screen, click the “Database” tab on the left. Enter your username and password: SalesUser and PowerAppsAreGreat!
      • Click OK.


  • Choose the Sales, Salespeople, and Goals tables. Click OK.


  • Click OK. This will import all of your data from SalesDB into a Power BI dataset.
  • We need to do a little data preparation before we can create our visuals.
  • Let’s add two columns so we can join the Sales table and the Goals table.
    • On the navigation pane on the left, click the “Data” tab. This is the tab in the middle.


  • On the right, next to the Goals table, click the Elipsis button (…). Click “New Column”.


  • In the DAX editor, enter the following DAX:

JoinKey = FORMAT(Month(Sales[OrderDate]), “#”) & “-” & YEAR(Sales[OrderDate]) & “-” & Sales[SalesPersonID]


  • The table should now look like this:


  • The JOIN column is month, year, and salespersonID concatenated. We’ll do the same thing for the Sales Table.
  • Click the ellipsis button for the Sales Table. Add a New Column. Enter the following DAX:

JoinKey = FORMAT(Month(Sales[OrderDate]), “#”) & “-” & YEAR(Sales[OrderDate]) & “-” & Sales[SalesPersonID]

  • Now let’s change the data model a little bit. Click Data Model tab, the bottom tab, on the left. Your screen should look like this:


  • Double-click on the relationship between the Goals table and the Sales table.
  • Make the screen look like the following screenshot. Choose the JOINKEY column for both tables. Verify that the cardinality is Many to One. Make sure the Cross filter direction is set to Both:


  • Change the relationship between the Sales and Salespeople table.
  • Make sure the relationship is Many to One. Make sure the Cross filter direction is set to Single.


  • Finally, we can add the visuals. We’ll create two simple visuals on the visual pane.
  • Click the Visual button on the left.


  • Add a slicer visual to the report
  • From the Sales table, drag OrderDate to the Field property.


  • Add a clustered bar chart to the report.
  • Add SalesPersonFullName (from the Salespeople table) to the Axis property. Add SaleAmount (from Sales table) and GoalAmount(from Goals table) to the Value property.


  • Your report should look like this:


  • You can clearly how the team is doing in relationship to their goal. As Salespeople make sales or enter goals into PowerApps, this dashboard will update. You can experiment with this by either inserting sales for Lana in Azure Data Studio and adding a goal for her in PowerApps for July, 2019. Come back to Power BI Desktop and click “Refresh” and see the new data in the report.

We have a hidden MVP in this solution in Azure Active Directory. Azure Active Directory users are the sharing mechanism in both PowerApps and Power BI. We can also grant AAD permissions to Azure SQL Database. This allows us to have a single sign-on between these products and easily know what users have access to which resources. This might seem trival and simple to people familiar with the Microsoft ecosystem, but it is very difficult to accomplish in competitive products.

And that’s it! We just created a great tool with PowerApps and Power BI. You can see that integration between these products is frictionless and powerful!

Ike

 

About the Author Ike Ellis

With over 18 years of experience in databases and a current Microsoft MVP, Ike has been Microsoft certified since the beginning, currently holding an MCDBA, MCSE, MCSD, and MCT.

Ike is the General Manager of Data & AI for Solliance. We have a full team of cloud data engineers and data scientists.   We specialize in building highly scalable data solutions for any size of organization.

Ike is a partner in Crafting Bytes, a San Diego software studio and Data Engineering group. We build software and BI solutions for companies all around the country.

In 2010, Ike founded the San Diego Tech Immersion Group (SDTIG). It has grown to be the largest user group in San Diego with over 125 active members including three other Microsoft MVPs. It is a technical book club that reads a book on a significant technical topic. Recent topics include Linux on Microsoft Azure, Angular 2/TypeScript, Data on Azure, Python for Data Scientists, and Docker/DevOps.   In July 2018, SDTIG started a track on Docker/Kubernetes. We will start a new track on Databricks/Spark in November 2018. You can join virtually and watch the youtube live stream: http://www.sdtig.com

Ike leads the San Diego Power BI and PowerApps user group that meets monthly. Find out more at https://www.pbiusergroup.com/communities/community-home?CommunityKey=a08275c5-90cb-4ba8-905b-646462aa1044

For more information, see http://www.ikeellis.com

 

 

Walk through creating the internal Microsoft PowerApps Tool “Thrive” application with Pat Dunn

 

PowerApps Guru’s Mehdi Slaoui Andaloussi, Venu Gopal Gaddamedi, and PowerApps celebrity Pat Dunn will walk through the PowerApps “Thrive” application.

 The PowerApps built, internal Microsoft tool called, Thrive, and is how Microsoft employees keeps up with company news, gets important alerts, see tasks at a glance, or see employee profiles, find and launch other Thrive apps.  In addition to being one of the widest used PowerApps at Microsoft it also has the distinction of having one of one of the highest NPS ratings!   In a recent Microsoft internal conferences 95% of the 4,000 attendees used this application for their entire conference experience of scheduling, evaluations and logistics. 

 In this webinar the team walk through their architecture enabling high performance and flexibility letting the team change setting of things like icons, descriptions, state, URLs etc. using app level configuration and user settings.

When 9/4/2019 8:00AM PST

Where: https://powerusers.microsoft.com/t5/Video-Gallery/Walk-through-creating-the-internal-Microsoft-PowerApps-Tool/td-p/351332

 

As a teaser wanted to include some screen shots of the Thrive Application:

News Feed

Application Launcher

Time Away

When 9/4/2019 8:00AM PST

Where: https://aka.ms/ThriveWebinar

About our presenter: Pat Dunn

 

 

Pat Dunn was one of the original authors of the PowerApps Canvas App Coding Standards and Guidelines whitepaper:  https://powerapps.microsoft.com/en-us/blog/powerapps-canvas-app-coding-standards-and-guidelines/

In his own words: Technical Program Manager for the Thrive suite of PowerApps within Microsoft. I design, build, and
support our employee experience and line of business PowerApps for over 120K employees.

Building PowerApps at scale, my team is a proxy to the enterprise customer and we’ve driven over
300 features and bugs into the Power platform while blogging on the experience, consulting with
customers, and authoring standards white papers.