Category: Simulation

Simulation Integration with Power Apps

On May 7, 2024

Simulating scenarios with Power BI is a common use case that organizations need to get the most out of their data. Being able to manipulate elements of the data to simulate an outcome brings deeper insights and accelerates decision making. Everyone says “what if”, but Power BI paired with Power Apps can help you build the simulation and put it into action.

About two years ago, I wrote an article on how to simulate scenarios with numeric parameters. This solution still applies and works well for most scenarios. However, there is a drawback to this solution. If you have a lot of inputs, you could end up with a painful user experience. Because of the constant recalculation of your measures, it could bog down your experience and eat away at your capacity.

While not a solution for every scenario, we can use Power Apps to achieve the same outcome. In our scenario for this article, we want to drive profitability by manipulating a profit and loss statement. We want to adjust inputs such as revenue, expenses, headcount, and taxes. Because of the numerous inputs that drive a profit and loss statement, this is a perfect example for Power Apps.

Preparing the Report Elements

To start, we are using an existing report that contains a profit and loss statement. We have a matrix visual that shows the lines of the statement and their associated values. If you notice, each line is labeled with a section letter and line number. This makes it easy to identify each element:

Screenshot of a report canvas in Power BI Desktop with a profit and loss statement. — *Profit and loss statement within Power BI Desktop*

To setup our simulation, we need to have measures to aggregate the data and bring it into the app. You will note that I created a Simulation App Measures query and added a measure for each line item. This will make it easy to bring the data into Power Apps.

Lastly, you will note that I left space on the right hand of the canvas to make it easy to add our application. While we are adding a Power App, it is like any other visualization and requires the appropriate space. While it is perfectly fine to place the app on it’s own page, we want to keep it close to our profit and loss statement for comparison.

Preparing the Power App

While it seems simple, we need to take a few steps to integrate Power Apps into our report. The first step is that we need to select the Power App visual in the visualization pane. Once you add the Power App visual, it provides the instructions for the next few steps:

Screenshot of adding a Power App visual to Power BI Desktop — *Adding the Power App visual to our report canvas.*

Once we have the visual in place, we need to add fields from our data pane to the visual. This data is the base for supporting your simulation. Once the data has been selected, we can select which Power Apps environment we want to use. Next, if we have an existing app, we can select it here. However, we have not created the app yet so we are going to create one from scratch. When you do this, you will be warned that a new window is being opened so you can edit the app with Power Apps Studio.

Creating Your Simulation App

Now, there are a lot of components that go into a Power App. Because of the complexities that go into creating a Power App, I am going to direct you to Microsoft Learn to get all of these details. However, I am going to go over a few basics to help you get started.

Basic App Settings

There are a number of settings you will need to get in place. A simple example is the name of the app, icon for the app, and the orientation of the application. If you go to the three apps at the end of the menu and select settings, you can set all of these items. You can also set other items such as background color or images:

Screenshot of Power Apps Studio and how to get to the app settings. — *Adjust settings for your app within Power Apps Studio*

You will also note that this is an option for version history. If you are new to Power Apps, this is a great tool to assist you in the event you make a mistake. You can easily roll back your version if you break something and need to go back. Just remember to save versions periodically with comments so you know where you are at in your versioning.

Using Data for Simulation

If you are familiar with Power Apps, you know you can bring data from various sources into a single application. However, if you go to the data view, you will find it empty. But you need data to build your simulation, so where is it at? There is an item on your tree view called “PowerBIIntegration” which holds your data in a tabular format:

Screenshot of Power Apps Studio showing the PowerBIIntegration data source for simulation. — *The PowerBIIntegration provides the simulation data for our app.*

Since we put only measures in this app and no dimensions with it, the table only has a single row in it. However, we will still need to provide the aggregation of our data. To do this, you will need to write a little code in Power Apps to achieve the desired value to appear in a text box. For example, if you want to aggregate the total gross revenue, you will need this code to achieve the outcome:

Text(Sum([@PowerBIIntegration].Data, A1), "$#,0")

This code aggregates the data from [@PowerBIIntegration] and formats it properly for display. You do not have to go as crazy, but I really like to make sure the formatting is correct. It requires a little extra work, but it goes a long way.

Testing, Saving, and Publishing the Simulation App

Once you have something ready, you can click the play button on the menu bar to test your application. This will allow you to test the functionality of your application and ensure it works. You also can hold down your ALT key to test elements on the app canvas. Once you have it tested, it is time to publish.

Test, save, and publish your reports from Power Apps Studio

Once it is ready, click the save button to ensure your changes are captured. Next, you can click the publish button and this will push the changes live. This will allow you to view the app inside of Power BI and available for your report consumers to use. Depending on the environment settings, you might need to share the application with others, but that can easily be performed with using the same Entra or Active Directory security group you leveraged to provide access to the report in the Power BI Service. However, when you are finished, the report canvas will now have the app integrated right on the page:

Screenshot of the simulation Power App inside of Power BI Desktop. — *The final product – Simulation app inside of the Power BI Report*

Common Questions with Power Apps

Every time I bring up this solution, I receive a lot of questions. However, they are often variations of the same three topics that are in this section. I wanted to address them as I am guessing you have the same questions as well.

Question 1 – Licensing and Cost

We all know Microsoft does not give much out for free. However, with how this app is designed, there is no additional impact to the cost of your licensing. Power Apps is using seeded capabilities from your base Microsoft 365 licensing and therefore does not cost you anything extra. While licensing changes often, I recommend you check out Michael Heath’s blog post on Power Platform licensing if you are new to it.

Question 2 – App Use Outside of Power BI

A lot of people ask about being able to leverage the app outside of Power BI. The answer on this is a hard no. We must keep in mind that the app is leveraging data from your Power BI report and filtering based upon the report context. Therefore, this app is not usable outside of the report. However, you could easily re-deploy this app in another report assuming you leverage the same measure names to load the data into the app.

Question 3 – Power Apps and Writeback

The last question I receive is if Power Apps provides capabilities for writeback to data sources. The short answer is yes, but depending on your data source additional licensing might be required. There are tools out there that allow you to perform write back with less complexity, but Power Apps absolutely works for this. It is up to you if it is worth the effort and cost to make it happen, but it is absolutely possible.

Conclusion

People forget that Power BI is actually part of the Power Platform suite. Sadly, many forget that there are opportunities to integrate these apps together. However, this is an example of being able to simplify integration of Power Apps to extend capabilities with little effort. If you are not an expert, do not be afraid to use Copilot for Power Apps to help you out as well!

Have you ever integrated a Power App into a Power BI report? What was your use case? Did you have any challenges in making it happen? Tell me in the comments below!

Which Tool When – Parameters in Power BI

By Dominick Raimato

On May 5, 2022

In Best Practices, Incremental Refresh, Power Query, Report Design, Simulation, Web Connector

The word parameters is a little ambiguous when it comes to Power BI. I have written a number of articles that reference parameters, but never took the time to clarify their use cases. I wanted to dive in to the different types of parameters and understand the best way to use them. Paired with some tips and tricks, you will have a better idea of how to use all kinds of parameters in your reports.

Parameters in Power Query

In my opinion, parameters in Power Query are the most in your face. When you launch the Power Query editor, there is a button on the home ribbon that helps you manage them. But have you ever used them?

Parameters in Power Query allow you to add reusable values in your queries. The premise is simple – if you keep using the same value over and over again, why not make it easy on yourself?

How to Leverage It

To get started, open the Manage Parameters window in Power Query. From there, create a new parameter and give it a meaningful name. From there, you just need to specify the type of value, add your value, and click OK. Since I am creating a parameter for a reusable API key, I am just using text and adding in my key.

Now that I have the parameter in place, I can use it in an existing query. Since I was looking to easily reuse an API key, I can go and update my queries.

Leveraging my Parameter with Another Query

As an added bonus, I can share my full query without having to remember to hide my API key!

Common Use Cases

While the API key use case above is one of my favorite stories to tell, there are a few other key use cases.

One of the most famous use cases is when you need to create parameters for Incremental Refresh or Hybrid Tables. You need to create a RangeStart and RangeEnd parameters to setup the filtering on the report.

Another great use case is when you need to quickly change values to filter a report. In a past life, I worked for a company that had APIs for external reporting. Since customers always wanted similar reports, I created them with parameters so I could quickly update the API key and customer id. When a new customer wanted a custom report, I just updated the parameters to get their data in, made some adjustments, and sent the report out.

Tips and Tricks

Tip #1 – If you save your Power BI Desktop file (PBIX) as a template (PBIT), you will be prompted to enter the parameters when you use it. This helps save you a few clicks.

Tip #2 – All of your parameters can easily be updated in the Power BI Service without having to open the desktop file. Go to your data source settings and you will find the parameters section. This allows you to update your parameters like API keys with minimal effort.

Tip #3 – If you are using a deployment pipeline, you can easily swap between your Dev-Test-Prod databases. Just set parameters for your server and database fields in the advanced editor window and update them in the Power BI Service.

Function Parameters

Not as common of a use case, but functional parameters allows you to reuse a query quickly. I go into great detail on why to use functions with my article on how to use REST APIs in Power BI, but in short, they allow us to scale a base query rapidly.

How to Leverage It

One of my favorite endpoints pulls stock information by ticker symbol. I could build multiple queries and append them, but a function makes it easier. I just need to add a little code at the top to convert my base query to accept a parameter.

I specified the parameter that I wanted to gather and then apply it in the query. When I go to use the function, I will be prompted to specify the parameter so the query works properly.

Common Use Cases

I frequently use function parameters with APIs. Because some API endpoints require dynamic values, you will need to iterate with a function like I did with the stock symbols.

Another common use case for me is building common elements I might use in a report. If I am running into queries that are using paging , I can grab a function and invoke it to build out my base. Another common use case is to build out a date table when one is not available in my data source. Of course these are things we want to do as far upstream as possible, but if you are not in control of your database, you might need to create your own.

Tips and Tricks

Tip #1 – Invoking a function as a column is an easy way to specify your parameters with existing column values. The function will run on each row of your query using the values in the columns identified. It is a quick and easy way to apply a function!

Tip #2 – You can edit a query and add a function by hand. Just make sure you specify your parameter in the function or it will not work. I use this all the time for token based authentication with APIs.

Tip #3 – If you are using a few different API endpoints, you can create a base function to handle the majority of your query. Just add a parameter to the function to specify the endpoint. From there, you can add your endpoint or table name in the parameter field and invoke the function which speeds up your connection time.

What If Parameters

What if parameters are different than the other two parameters we discussed. Power Query and function parameters really help us with querying data efficiently. What if parameters are all about what you could do with your data.

How to Leverage It

We often find what if parameters when you want to run some simulated scenarios. I recently did a deep dive on what if parameters, but at a high level, we have two components.

The first component is the creating the parameter itself. We will go to the modeling ribbon and select new parameter. A new window will open and I can specify key components of my parameter which include the name, data type, minimum, maximum, increment value, and default. Once I click OK, a new slicer will appear.

But that was the easy part. We now have a new measure that is <Parameter Name> Value. This brings back the value you selected in your parameter. We now can use this measure in a measure to get our value. You will need to use a little DAX, but it is worth the extra effort.

Common Use Cases

The most common use case is to simulate revenue increases. I add the parameter to a formula that takes their current revenue and shows the change. Everyone is trying to make more money, so it makes sense.

One of my favorite use cases is to help filter data. I built a report that compared property assessments in my town. I used a what if parameter to help filter 3,500 properties to only a handful. Using key values such as year built, livable square footage, and lot size, the parameters found similar houses to the one I selected. Instead of having to fool around with filters, I could just quickly adjust my report parameters instead.

Tips and Tricks

Tip #1 – Always have the parameter make the slicer when you can. You can hide it in the selection panel if you don’t want to use it, but the slider is so nice. It makes the mobile experience even better!

Tip #2 – If you are using a number of parameters, don’t be afraid to create a page for your parameters. You can just sync the slicers to other report pages. This prevents clutter and speeds up load time for a page sine there are less objects in the view.

Tip #3 – You can create your own what if parameters with a custom table. This is nice when you want to use a non-numeric parameter. This is a common practice for filtering reports on things like top/bottom views.

Parameters on Parameters

You might have gotten to this point and said “Wow – I had no clue that this term is so widely used!”. I think it is important to understand the different kinds of parameters because they really can make an impact on your reports. Some of my reports maybe only use one of these while others use all three. The key is understanding how to properly use them.

Have you used any of these parameters before? Do you find them helpful in your report design and data models? If so, tell me in the comments below!

What If Scenarios in a Power BI Data Model

By Dominick Raimato

On April 7, 2022

In DAX, Report Design, Simulation

What If? A common question we ask all the time. What if we increased our revenue 20% from last year? What if we increased our margins by 2%? We ask these questions frequently, but do we do anything to solidify next steps to figure these things out?

Power BI makes it easy for us to run these scenarios in a dynamic format with What If Parameters. However, I find most people avoid using them because they need to do some DAX to integrate them. Today, I want to demystify this and help you build out your own scenarios. Our scenario will allow us to create a revenue goal that we can adjust based upon the previous year.

Setting Up Our Revenue Measures

To get started, it is important to build a measure to calculate both our current year and previous year revenue. This will provide a base to perform our comparisons.

I start with a base calculation that will summarize our sales data for the current year:

CY Revenue = 
SUM(SalesAggregated[Revenue])

Which results in this column chart:

Next, we will leverage our current year revenue to calculate the past year’s revenue:

PY Revenue = 
CALCULATE([CY Revenue],   SAMEPERIODLASTYEAR(SalesAggregated[Month]))

This results in the clustered column chart we created in my article last week:

The result of our measures allows us to see the previous year’s revenue for all of 2021, but only the current year revenue for Q1 of 2022. Our goal is to next add a What If Parameter so we can create our 2022 goals.

How Does a What If Parameter Work?

What If Parameters are built out in the Power BI Report. In fact, when you create a parameter, it does a lot of the hard work for you. Let’s start by creating a parameter and see what happens in the background.

Creating a What If Parameter

To get started, go to the modeling tab and select new parameter. A new window will appear which will require some input. First thing we will want to do is set our parameter name. I am calling mine “Revenue Increase %”.

Next, we need to configure the parameter. Because we are working with percentages, we need to change the data type to decimal number. Then, we need to set our minimum and maximum values. Because we do not want to lose revenue, our minimum will be zero. Our maximum will be 2 which will allow a 200% increase of revenue.

Lastly, we need to set the incremental value. We are going to specify .05 which will allow 5% increments. We will also set our default our value at zero. You will also notice that there is a box checked to add a slicer to the report canvas. We will leave that checked and click ok.

Setting Up a What If Parameter in Power BI

The most obvious item created is the slicer that appears on the canvas. But there is so much more to this simple process that happens in the background.

What Else Is Created With a What If Parameter?

To answer that question, let’s head over to the table view of the report. A new calculated table shows up on the left hand side that matches the name of our parameter. When we select it, you can see there is only one column which lists our percentages from 0 to 200 in decimal format.

But how do we leverage these numbers? The slicer will limit which value is being used, but how do I build a relationship to my revenue? While there is only one column on the table, there is a measure that was created as well:

Revenue Increase % Value = 
SELECTEDVALUE('Revenue Increase %'[Revenue Increase %], 0)

The SELECTEDVALUE() function looks for the value you have selected with the slicer and returns a single value. In this scenario, it is looking for a single percentage value in that column that was created. If it finds more than one value, it returns a zero instead.

Is There Any Other Setup Required Before We Use It?

At this point, everything is in place for a usable What If Parameter. However, I am always a fan of cosmetics and making things look a little cleaner. Everything is currently in a decimal format, but we are looking at everything in percentages. As a result, we will want to select both the column and the measure created and make sure they are formatted as a percentage with no values after the decimal place.

Formatting What If Parameters for Percentages

We can also consider adding a card to help highlight the percentage we want to use over last years numbers so it is easy to find. We can use that measure created to make it easier to see on the report canvas. This is helpful if you are syncing the slicer across multiple report pages.

Our What If Parameter on the Report Canvas

It is important to understand what is being built in the background because it lays the groundwork for using the What If Parameter in our report.

Applying a What If Parameter

Now that our parameter is set, we need to apply it to our report. The first step is creating a new measure that will calculate our goal.

CY Goal = 
[PY Revenue] * ([Revenue Increase % Value] + 1)

If you remember, our goal was to calculate our current year goal as a percentage over our previous year revenue. To accomplish this, we used our previous year measure and multiplied it against the measure we created from our What If Parameter and added one. Why add one? Because we want to grow our revenue, so we add one to set the base as 100% and can grow it from there.

With our new measure created, we can apply it to our column chart:

Previous Year Revenue, Current Year Revenue, and Current Year Goal in a Clustered Column Chart

Now that everything is in place, you can adjust the goal at the beginning of the year to set the goal. At the end of the year, you can easily adjust the goal to align with what happened over the entire year to determine what your goal is for next year.

Next Steps

We started with a single What If Parameter, but if you have multiple you want to adjust, you can create more of them. In the event that you have a complicated list of values that has a lot of rules, you can consider some other options as well. While we did create everything with a few clicks, you have the ability to write all of the DAX on your own. You can build out a table, create the slicer, and a measure to leverage it. The reality is you can create flexible parameters to meet any scenario!

How about you? Do you have any reports that could benefit from a little flexibility? Have you used What If Parameters before? If so, tell me in the comments below!