Creating dynamic Power BI tiles inside of PowerApps

Related demo can be found here:…


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:


Upload the report to Power


  1. Please log into Power BI using the supplied accounts i.e.


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



  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.


  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


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:



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


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.

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

exists SalesPeople;

exists goals;

exists Sales;


table SalesPeople

(SysID int
identity(1,1) PRIMARY

, SalesPersonFullName varchar(100))


into SalesPeople



(‘Sterling Sanchez’)

, (‘Rick Archer’)

, (‘Morty Figgis’)

, (‘Lana Smith’)


table Sales

(SysID int
identity(1,1) PRIMARY

, CustomerName varchar(100)

, OrderDate datetime

, SalesPersonID int

, SaleAmount decimal(14,2))


table Goals

(SysID int
identity(1,1) PRIMARY

, SalesPersonID int

, GoalMonth int

, GoalYear int

, GoalAmount decimal(14,2))


into Sales

(CustomerName, OrderDate, SalesPersonID, SaleAmount)


(‘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’)


ROLE db_datareader ADD MEMBER SalesUser;

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 “”.
    • 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:

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:

SalesPersonID = ddlSalesperson.SelectedText.SysID,
GoalMonth = ddlGoalMonth.SelectedText.Val,
GoalYear = Value(ddlGoalYear.SelectedText.Value)
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!



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:

Ike leads the San Diego Power BI and PowerApps user group that meets monthly. Find out more at

For more information, see



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



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


About our presenter: Pat Dunn



Pat Dunn was one of the original authors of the PowerApps Canvas App Coding Standards and Guidelines whitepaper:

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.

Good by Live Writer HELLO Word Blog Templates


Lately I have been posting a lot of blog posts for my marketing brothers and sisters and unfortunately, they often (try to) give them to me in normal word or email format.

…Of course, pasting that into WordPress makes a mess of the formatting and loses all the images. The answer is really straight forward: the Word Blog Template.

Getting started is as easy as File > New and search for the word blog!

At this point word will take care of the image uploading and does a good job of preserving the formatting


This then gives you a striped down version of word that knows how to upload images, format to html etc etc



PowerApps User Group in Seattle Sept 18th


Join us for our 3rd PowerApps user group meeting here in the Seattle area!

These meetings are designed to help those new to the tool as well as those who have been with the tool since day one.

We look to leverage the meetings to engage with each other and the Microsoft team to help everyone along their learning journey.

We encourage you to come with questions and come along on this journey with us.

This month’s Agenda:

  1. How to get started with PowerApps Portals by Dileep Singh
  2. PowerApps & Flow Tips for Admins by our very own UG Leader Mandar Zope
  3. Microsoft Flow best practices with Maricel Medina
  4. Time providing PowerApps MVP, Ted Pattison, to show how to create PowerApps PCF controls



September 18th, 2019 4-7pm PST


Microsoft Advanta C – Conf Room ADVANTA-C/4051
3003 160th Ave SE
Bellevue, WA 98007

Please RSVP as space limited to the first 24 people!!!!!!!!!!!!