Category: Report Design Page 1 of 2

Extending Power BI with Power Automate

On February 17, 2025

While Power BI has always been a part of the Power Platform, it never feels that way. So often the focus has been integrating Power BI into Power Apps. But what about Power Automate? Is there a way for us to integrate some automation with our Power BI content?

The short answer is yes! There are a lot of components to consider with your solution. Spend a little time brainstorming and you will discover a world of possibilities! The trick is to consider what value that automation can bring with just a little innovation!

What is Power Automate?

Power Automate is the automation pillar within the Microsoft Power Platform. Focused on repetitive and menial tasks, Power Automate is designed to free your time to focus on more value-add activities. You create “flows” that start with a trigger and then have subsequent actions that are executed within the context of the flow.

To create a flow, you can navigate to Power Automate and sign in. From there, you can start typing into Copilot to tell it what you want to do. However, you might want to just explore some ideas. For that, you can go to the templates section on the right or just click create to start from scratch!

Screenshot of the Power Automate home page with Copilot in the center and the navigation bar on the left for templates and creating solutions from scratch. — *Screenshot of the Power Automate web interface*

To help you with your journey, let’s take a moment to understand what triggers and actions are.

Flow Triggers

When you create a new flow, you must initiate it with a trigger. There are three primary types of triggers:

Automated Cloud Flows
Instant Cloud Flows
Scheduled Cloud Flows

Screenshot of how to create a flow from scratch in Power Automate — *Screenshot of how to create a flow from scratch*

While there are some other options to use on the create screen, we will focus on these three. The difference between the triggers is pretty simple. Automated cloud flows initiate when something happens like a new file is created or an email arrives. Scheduled flows happen on a particular cadence. This is just like a scheduled refresh in Power BI. Instant flows are initiated with a click of a button.

Flow Actions

Now that you have created a flow and assigned a trigger, you must assign at least one action. Actions are essentially automation steps that can be taken within the flow. You can click the plus sign under your trigger to add an action:

Screenshot of adding an action inside of a flow with Power Automate — *Adding an action in a flow*

On the left-hand side of the editing screen, you can search for different actions or applications. You can select an application to see what actions are available. A great option to look at is Microsoft Teams. Some actions you can do include create a message or chat, schedule a Teams Meeting, or post an adaptive card and await a response as just a few options:

Screenshot of actions available for Microsoft Teams within the editing window of Power Automate — *List of actions that can be taken with Microsoft Teams*

One other benefit within Power Automate is that we can call data from the initial trigger and previous actions. In our example, this flow is being manually initiated with a button from the Power Automate app. By initiating the flow in the mobile app, we are able to capture some location data. I can call that data dynamically within my flow:

Screenshot showing the list of location data elements available from the flow trigger within an action for Power Automate — *Calling dynamic data from the flow trigger in an action*

At this point, I hope you are already thinking about what actions you could take with Power BI inside of Power Automate!

What about this “Premium” label?

This always comes up when I talk about anything with the Power Platform. What does the premium label mean? In short, it means you need to pay for some additional licensing to use this action or trigger.

When you look at the actions and triggers available to you, there are generally three types. Built in items are always available. Standard items are available assuming you have the application licensed and enabled for the user. This generally applies to the entire Microsoft 365 stack in addition to some other third-party SaaS solutions. Examples include Google Drive, Gmail, Dropbox, Box.com, and others of that ilk.

The last group, premium items, require you to pay for some form of premium licensing. This includes SQL connectors, Azure DevOps, Dataverse, AWS services, and others. If you want to use data out of your SQL server, you need to have a premium connector.

Or do you?

Power BI Actions in Power Automate

If you know me, you know that I like doing things as efficiently as possible. I hate watching my customers pay for licensing they do not need. I also do not condone the circumvention of licensing and recommend you invest to do things the right way every time. With that said, sometimes we can get creative with our solutions to obtain a goal.

This is why I feel getting a plan together early helps. If you understand the actions and touch points you are looking to use, you might be able to save a few dollars here and there. Not every organization has funds available to achieve goals. However, a little strategy could get you there quickly!

Querying your Semantic Model

If you take a look at the actions you have available to you for Power BI, you will note that there are several options. One of those options is to query a Power BI dataset. Yes, it should say semantic model instead, but Power Automate has not caught up yet. If you leverage this action, you can write a query in DAX and run it against your model. Below is a sample query I have that I can use with my Van Arsdel Sales model:

DEFINE
	VAR _SalesSummary = 
		SUMMARIZECOLUMNS(
			'bi_manufacturer'[Manufacturer], 
			'bi_product'[Segment],
			"Quantity", 'ReportMeasures'[Quantity],
			"Revenue", 'ReportMeasures'[Revenue]
		)

EVALUATE
	_SalesSummary

ORDER BY
	[Revenue] DESC, 
		'bi_manufacturer'[Manufacturer], 
		'bi_product'[Segment]

This is a simple query that I wrote, but I can now apply it against my model:

Screenshot of the Run a query against a dataset action being customized to use a DAX Query inside of the Power Automate editor. — *DAX Query from above to be run against a dataset*

Now think about this for a minute – I just used Power Automate to query data that is being refreshed from my SQL Server! Am I violating the licensing terms? No! Instead, I am leveraging the existing investment I have made in Power BI licensing. That is pretty cool!

Now, this might not always work for everyone. Real time data would still need to connect to a SQL server directly. However, if that is not a requirement, you might be able to avoid buying additional licenses by thinking through how you connect to your data in Power Automate.

But what else could I do?

Export Reports to a File

I can already hear the groans on this one. However, hear me out. For many organizations, they would like to create a way to share reports and dashboards with customers. Exporting this data to a file does take away the interactive piece. But is there a valid use case for this? There might be!

The first thing I can do is identify the report I want to export to a file. You select the workspace and report that will be exported and define the parameters for the job. You can select file to be in PDF, PNG, or PowerPoint format. In addition, you can provide a filter like I did on the manufacturer. I am not going to bore you with the filtering details, but you can reference this Microsoft Learn article for more information. I also suppressed my hidden pages for the time being:

Screenshot of the Export to File for Power BI Reports action being customized within the Power Automate editor. — *Exporting a Power BI Report to a File*

Now that I have an action exporting the data, I can attach it to an outbound email. For that, I just need to place the dynamic content from the export into the attachment field and I am good to go:

Customizing the Send an email (V2) action for Outlook to send an exported report in the Power Automate editor. — *Attaching the exported report to an email*

Now I know some of you are already thinking about ways to reduce your licensing already with Power BI Capacity or Fabric Capacity. Remember, you are leveraging existing licensing. Unfortunately, this one requires you to still maintain capacity licensing on the Power BI side. But why would I do this instead of sharing it directly with others or embedding into a web portal?

What I like about this approach is that you could create a list of customers and contacts that you could put together and simply send these reports. If you are not a web developer or comfortable with the idea of external sharing, you could put this together pretty quickly. As long as your report is within a workspace that has a capacity assigned, you could do this on your own with just a few clicks! Pretty cool!

Any Other Interesting Actions?

Of course! For me, I think the dataset refresh and add a row to a dataset actions are the most helpful. They have specific uses and are not for everyone. But they have served me well in the past for certain edge cases or challenges that I am trying to solve.

In addition to those, there is a full complement of actions and triggers for goals. I find not a lot of organizations are leveraging goals, so they ignore these actions. Especially since they are officially in “preview” status as of the writing of this article. However, you might find a solid use case for them.

Power Automate Inside Power BI

Alright – we finally arrived at the moment you have been waiting for. How do I leverage Power Automate inside of Power BI? Well, it is quite simple. Just start by adding the Power Automate visualization to your report canvas like so:

Screenshot of adding the Power Automate visual to the canvas using the Visualization Pane inside of the Power BI Desktop interface. — *Power Automate visual inside of the Power BI Report canvas*

From there, the steps are spelled out for you. Make sure you have the right environment selected, then start adding data. Once that is complete, you can click on the three dots and edit your flow:

Screenshot of editing the Power Automate flow to take the Power BI data, convert it to a CSV table, and then create a CSV file in my OneDrive with the transformed data within the Power Automate classic editor. — *Editing the flow*

Now there are a few things to note. First is that you have to use the classic editor for this. I do not have a solid answer to why this is the case, but it is just the reality of the situation. While the interface is not as nice, the same principals apply.

Next, you will note that I have a data operation step. This is to simply extract the data I care about and strip away the additional metadata from the JSON body that comes in. I am then able to take that body and do whatever I want with it. As a simple example, I am exporting it to a CSV file inside of my OneDrive.

Once I am finished with my customization of the flow, I can save and apply. And while I like the Power Automate blue, it does not match my theme. So, I can just make a quick adjustment to the fill color so it matches. I also update the button text to identify what will happen when I click it. This way everyone knows what it will do when you press it:

Screenshot of the Power Automate visualization on the report canvas being updated with the proper matching theme color and button text stating "Run Export in SAP Job". — *Updating the button to match the theme*

Once again, I am hearing some grumbling from people reading this article. Yes, I am with you that exporting to Excel or CSV from a Power BI report is not what we want our consumers to do. However, there are valid use cases for this type of process.

I had a customer recently that would export data from a legacy system into an Excel file so it could be ingested into their ERP solution. Now I think we would both agree, if you are using Power Automate, why not write a real integration? 100% agree with you on that. However, remember what I said about leveraging your existing investments?

This customer created this custom ingestion method almost ten years ago. And while it is probably time for an upgrade, someone was able to create this solution with about three hours of their time. The time savings to the user was minimal as it might have taken as much as ten minutes once every few days to do this process the old way. But likely not enough to justify rebuilding the process to create a new integration. So, while it was ugly, it got the job done while providing a quick return on investment.

Conclusion

These are only some small ideas of what you can accomplish with Power Automate within Power BI. You are only limited by your own imagination. Spend some time thinking about what you might be able to do with this solution as this could really extend the value you are creating within Power BI!

Also, if you are looking to learn more about how to use Power Automate, check out my friend Micheal Heath’s Get Automating YouTube channel. You can also check out more resources from Microsoft Learn as you start your automation journey!

Simulation Integration with Power Apps

By Dominick Raimato

On May 7, 2024

In Report Design, Simulation

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!

Drill Through for Deeper Insights with Power BI

By Dominick Raimato

On March 12, 2024

In Look and Feel, Report Design

It is a pet peeve of mine and it drive me nuts. I hate it when someone tries to fit as many visuals on the canvas as humanly possible. It is cluttered, ugly, and hurts the performance of your report. But how do we get around this issue? The answer is simple! Add a drill through to your report!

I will be honest – I was confused by the initial setup like you might be. The setup is a little intimidating at first, but once you understand how to configure the drill through, it is easy to implement. Like what I said at the end of my article about bookmarks in Power BI, you will probably get things wrong the first time. However, with some guidance, I hope you will be able to implement them with ease!

Preparing Your Report

Generally, this starts with a basic report page with a goal. Right now, I have a Sales Data report page that has a lot of information. However, I want to drill down on a manufacturer and learn more about the products being sold. If you look at our current Sales Data tab, you will note that there is some product information, but not the context I want:

Screenshot of the Sales Data report page with a line chart comparing year over year of sales data, revenue by state on a map, a table of the top 25 products and their manufacturer by revenue, and a bar chart of quantity sold by manufacturer. — *Current state of the Sales Data report*

Our first challenge is to create a meaningful drilldown page that shows information by manufacturer. In my scenario, I wanted to see the top products by both revenue and quantity. I also wanted to see product segments and have the ability to filter by category. The result is a drilldown page that looks like this:

Screenshot of the manufacturer drilldown that shows two tables that return the top 20 products by revenue and quantity respectively. There is a donut chat showing distribution of revenue by segment and a tile slicer for product category. — *Manufacturer drilldown report page*

You will note that I did not place a slicer for manufacturer on the canvas. This is because the drill through will handle the filtering for us. We will tackle that next.

Add Drill Through Logic

With our detailed report created, we need to enable the drill through functionality. On the report page, we must add the field we want to use for the drill through. Since we want to look at the manufacturer, we will add that field to our visualization pane under drill through:

Adding the manufacturer field from the data panel to the drill through section for the visualization panel. — *Adding the drill through field*

One thing you might not have noticed right away is the back arrow that was added to the page in the upper left hand corner. This allows you to return to the original page when you drill down on your data. By default, this is set to just go back to the last page. However, you might want to hard code a page navigation. This will ensure you return to the correct page and is a requirement if you allow drill through on multiple levels:

Screenshot of the back button that is added to the page along with the configuration of the button. — *Configuring the back button added to the report canvas*

You will note that with the drill through, there were two additional settings available to us. The first setting is on by default which is to keep all filters. This simply maintains the filtering from the page where the drill through occurred. So if it is filtered on a specific year, it will be maintained.

The cross-report drill through allows you to make this report page available on other reports. When published to the Power BI service, other reports in the workspace can drill through with this page and see the detail. This allows you to reuse this page on multiple reports which is very convenient.

Testing the Drill Through

With everything in place, it is time to test our drill through. Going back to the Sales Data page, we can right click on one of the bars in our quantity by revenue visual. From there, we can see the drill through menu option with our new report page:

Screenshot of the drill through menu on our quantity by manufacturer visualization. — *Testing our drill through settings*

If you have the ability to select your new page, then you did it right! Go ahead and click on it and you will see the new detail page filtered on the manufacturer:

Screenshot of the drill through report page filtered on a manufacturer. — *Drill through report page filtered for Van Arsdel*

It take a little work to configure, but the results are beautiful. It does a lot to help clean up your canvas and allow you to get more detail on a specific dimension. With a clean look like this, it makes it easier to consume your report with the ability to dig further for details.

Anything Else to Consider?

What if you want to use other fields for the drill through on the same report? You can easily facilitate that by adding them to the drill through field. This will give you the ability to use the same page for multiple dimensions to reduce effort.

In addition, consider the detail of your page when designing it. The ability to reuse it over and over again will reduce a lot of repeated effort. If you leverage the cross report feature, it could make your drill through page an extremely helpful feature to any report you create.

Conclusion

This is one of those unsung heroes of the Power BI Report. It is hidden in plain sight and not always easy to create. But with a little effort, it can really transform your report. It is worth the time as it will take the experience of your reports to the next level!

So, have you used drill throughs on your reports? What are your use cases? Did I miss anything? If so, tell me in the comments below!

Self Service Analytics Does Not Equal Easy

By Dominick Raimato

On February 20, 2024

In Best Practices, DAX, General, Power Query, Report Design

This is a difficult post to write. From a personal perspective, I really struggle with this topic. In my daily work, I assist organizations with enabling a self service data strategy. Collaborating together, we spend hours working to find the right path that matches the needs and goals of the organization with the realities of the organization. I am a huge advocate for self service analytics and believe in it. Because of this, I want to be ridiculously helpful with everyone I encounter.

Sadly, I run into people on a regular basis that want “a little help” to perform some analysis with Power BI or Excel. The problem with this is that the ask is quite complicated. The reality of my situation is that I can make the ask happen quickly. I can spend an hour or two on a report and it will look beautiful. Having worked with Power BI for almost ten years, I have a pile of experience to make it happen efficiently. But the requestor wants me to help them build it themselves. This takes three to five times longer because I am guiding someone through the process. And I am all for doing this because it helps enable another person to use Power BI or Excel.

While I am all for helping people, I find that their interest in learning about these tools is often non-existent. They have a goal in mind that needs to be completed and they think Power BI or Excel is an easy way out. It is a self service analytics tool, so it should be easy! And yet they find the opposite. Without taking taking the time to understand the basics, they end up lost.

What makes this hard for myself and others is that it cheapens the skills we possess as data professionals. Data is not easy. Personally, I find this approach to be a little insulting. So how do we fix this problem?

Setting Expectations for Self Service Analytics

I started responding to every request I receive with clear expectations around self service analytics. I explain the challenges and skills they need to possess before they embark on such a project. Sometimes this scares them off, but it is reasonable way to find alignment.

I often share the basics they will need to learn to be successful. I start with understanding data modeling. We start by talking about things like building a star schema, using a date table, and managing relationships. Sometimes a flat table from Excel or SharePoint is fine, but other times they want more depth and dimension to their reports. Without this in place, they will never achieve their goal.

Next, we discuss how to build out measures. Like every beginner, including myself at one time, they rely on implicit measures. These measures often provide basic aggregations and get the job done. However, they often ask for something more complex. They need to perform time based analysis or use filtering. They are going to need to learn how to write DAX expressions for explicit measures.

Lastly, we will discuss what report design looks like. Often, I have a theme file or template for the organization available, so this helps with branding. But it is important to address best practices with data visualizations. This avoids confusion and provides a better consumer experience.

Assign Some Homework

With expectations set, it is time to assign some homework. Self service analytics requires some investment. But before helping anyone, I assign some basic training. The concept is simple – if someone does not know the basics, they are never going to understand the advanced topics.

Microsoft Learn is my go to for training others around the basics of Power BI. Because it is free, the only real investment is time. It covers a lot of the basics of the tool and allows me to focus on the more advanced topics. I, just like you, need to protect my time. The more I can push to other resources, the easier it is for me to stay focused on my work.

There are two benefits from using this method. First, I can ask for a badge validating that they completed the training. However, I like that this method helps gauge the interest of someone who is asking for help. The course linked above takes about three hours to complete. If they cannot invest three hours in themselves, then why should you invest three hours in them? The answer to that question is up to you, but it sets expectations for the relationship you will encounter through this engagement.

Create Working Sessions

At this point, if they have completed their homework, I recommend creating working sessions to set boundaries. I start with setting up 45 minute sessions with a clear agenda. Ideally, a weekly working session is best. Naturally, you might need to schedule more frequent sessions based upon the business requirements. It is important that you agree upon the cadence to avoid conflict in the future.

For each working session, start with a recap of the previous session. Review the previous topic and any progress made since you last met. Then, work through the assigned topic for the current week. Make sure it is recorded so it can be referenced afterwards. Lastly, decide what the topic of the next working session. This helps keep future sessions focused and your timeline compact.

I recommend that you continue assigning homework. For example, if you have an upcoming working session that will focus on a particular DAX statement, provide resources ahead of time. If you have a measure that needs to use the SWITCH() function, send them to an article like this one to help them understand the concept ahead of time. You can also assign work to be completed in their report before the next session to streamline your working sessions.

As a rule of thumb, I work to keep sessions scheduled. An ad-hoc working session often ends up being a mess. You also have other deliverables and expectations from your leadership that could get in the way. If someone needs time with me between sessions, I generally ask them to schedule it. I feel like the “Can we hop on a quick call? I have a quick question!” conversation always comes at the most inopportune time. Setting these boundaries goes a long way to ensuring you can maintain a balance in your workday.

Provide Some Learning

It never hurts to provide additional learning and training. There are several sources out there. There are several books and sites out there to help. If you need help with Power Query, you can check out my book as an option to assist. If it is with DAX, I recommend The Definitive Guide to DAX by Marco Russo and Alberto Ferrari. Lastly, you can suggest Now You See It by Stephen Few if they need help with data visualization.

At some point, you they will need to embrace self service analytics on their own. By providing materials, they can be focused on their learning and be able to sustain their own solutions. These resources will provide assistance both during your working sessions and in the future.

If self service analytics is a goal, investment in learning is key. I see customers who continue to struggle with this because they do not invest in training and learning. When you are working with an individual, at some point they will need to invest more in themselves to achieve that goal. If you do not help them head down that path, you will be stuck helping them forever.

Conclusion

I love helping people discover the world of self service analytics! I hope you are too! Just make sure you help others in a sustainable manner. These types of relationships are good to have, but you need to manage them well. Be ridiculously helpful – just take care of yourself as well!

Have you encountered this situation? Do you struggle with people who think self service analytics is easy? If so, tell me about it in the comments below!

Dynamically Compare Customers in a Report

By Dominick Raimato

On February 6, 2024

In DAX, Field Parameters, Report Design, Uncategorized

I was working with a customer recently and she came up with an interesting use case with Power BI. The idea was simple – she needs to compare a manufacturer against the top three manufacturers within a period of time. Thinking it was pretty straight forward, I suggested she just use a bar chart and filter down to those manufacturers and call it a day. If only it were that simple!

She came back and said well I need to dynamically select the chosen manufacturers. And in a perfect world, I would like to change the threshold of top manufacturers. Naturally, this whole scenario became far more complex! Being someone who does not back down from a challenge, I decided to give it a shot!

To help break down how this will work, I decided to use DAX Studio. It is a free tool that you can download and install from here. This is not a prerequisite, but it does make explaining the measure easier.

Part 1 – Design the measure in DAX Studio

The first step in the process is that we have to build out our base query. To start, we will pull back a list of manufacturers and the revenue associated with them. We will return that query ordered by the manufacturer with the highest revenue at the top:

DAX Studio window with a list of manufacturers and their associated revenue totals. — List of all manufacturers and their associated revenue totals

Next, we need to limit the list to only the top number of manufacturers selected. We will add a variable with a hard coded value of three for now. However, we will make it dynamic later on. We also need to add a filter to our list of manufacturers so only the top three return. We will use RANKX() to achieve that result.

Query in DAX Studio updated to only return the top three manufacturers and their associated revenue totals. — Returning the top three manufacturers by revenue

Next, we want to declare a manufacturer and return the associated revenue. For now, we will hard code the manufacturer Fama and update it later. We will then return the revenue associated with our selected manufacturer.

Screenshot of DAX Studio showing the top three manufacturers revenue plus our revenue for Fama. — Final code from DAX Studio

If you are following along at home, the final code for what we put together in DAX Studio is as follows:

DEFINE
    VAR TopRank = 3
    VAR SelectedManufacturer = "Fama"
    VAR ListOfManufacturers =
        ADDCOLUMNS (
            SUMMARIZE ( bi_manufacturer, bi_manufacturer[Manufacturer] ),
            "Revenue", [Revenue]
        )
    VAR FilteredList =
        FILTER (
            ListOfManufacturers,
            RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
        )
    VAR SelectedManufacturerRevenue =
        CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
    VAR UnifiedList =
        UNION (
            FilteredList,
            { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
        )

EVALUATE
UnifiedList
ORDER BY [Revenue] DESC

With that set, we can now turn our attention back to Power BI to prepare our report for the next step.

Part 2 – Preparing Power BI

There are two elements we must prepare for this scenario. First, we must prepare an independent manufacturing list. To avoid any conflicts with filtering, we must create this as a new query that parallels our existing bi_manufacturing table and is used in our calculation.

Next, we will need to add our top manufacturer threshold selection. Right now we hard coded the value three in our formula, but we want it to be dynamic. We will use a what-if parameter to achieve that goal.

Preparing the manufacturer list

The next step in our process is to prepare a list of distinct manufacturers. You could choose to create a DAX table to achieve this, but I suggest performing this in Power Query. I simply referenced my bi_manufacturer table, removed my extra columns, and updated the name to ManufacturerList.

Image of my prepared manufacturer list inside of Power Query. — List of manufacturers in Power Query

Next, I will need to create a measure to identify the selected manufacturer. This will allow me to select my manufacturer from a list. And if a single value has not been selected, it will return a blank value instead.

Selected Manufacturer = SELECTEDVALUE(ManufacturerList[Manufacturer], "")

With all of this in place, we just need to add a slicer on our report page.

Screenshot of our Top N Compare report canvas with the Manufacturer slicer present and set to Fama. — Top N Compare report page with Fama selected from the ManufacturerList

With this in place, it is time to add our threshold measure.

Preparing the Top N selector

In the past, I wrote an article on leveraging What If Parameters in Power BI. In that scenario, we used a decimal value for percentages. Instead, we will use whole numbers for this situation. However, a small change has occurred since that article was published. Now, there are two types of parameters we can select. We will be selecting the numeric parameter to achieve our goal. Head to the modeling ribbon and select the numeric range option from the new parameter dropdown. You will need to update the highlighted fields below:

Screenshot of selecting and updating a numeric range parameter in Power BI. — Adding a numeric range parameter in Power BI

Leaving the add slicer option checked, click create. This will generate a new table and measure that will work in concert with the new slicer. We do not have to do anything extra at this point, but it is worth noting that you could choose to create these elements manually if you choose.

Part 3 – Modify DAX Studio code for use in Power BI

Time for the grand finale! With all our hard work, the last few steps are about making some small adjustments to our DAX Studio code. We will start by creating a new measure and adapting our code for use in Power BI.

Taking the code from above, the first step is to remove the word DEFINE from the block. Next, we will update our hard coded values for top rank and selected manufacturer. If you create a report measures table, note that the [Top N Manufacturer Value] will not appear there unless you move it. Check under that new table we created.

Next, we will add a new variable at the top called CustomerLine and use it to bridge our revenue to the right customer. The code will look like this:

VAR ManufacturerLine = MAX(bi_manufacturer[Manufacturer])

Lastly, we will change our EVALUATE to a RETURN and update the value to perform a SUMX() on the revenue of our unified list where the ManufacturerLine value matches the Manufacturer. Our final code for our measure should look like this:


VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( bi_manufacturer, bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )

At this point, I added my Top N Revenue measure to my table to compare and I discovered two issues. One, it is returning revenue for every line, not just the Top N and the selected manufacturer. But in addition, it is doubling the revenue for Fama:

Screenshot of issues from our new Top N Revenue measure. — Top N Revenue measure not working properly

To fix every value coming back, we will simply add an ALL() around our original list of manufacturers. This will make sure all manufacturers are accounted for in this calculation:


VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( bi_manufacturer ), bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )

To avoid a potential collision where our selected manufacturer is also in our top list, we just need to add some logic in our return statement to see if our manufacturer is present. If it is, simply return the value from the top list and move on.

VAR TopRank = [Top N Manufacturers Value]
VAR SelectedManufacturer = [Selected Manufacturer]
VAR ManufacturerLine =
    MAX ( bi_manufacturer[Manufacturer] )
VAR ListOfManufacturers =
    ADDCOLUMNS (
        SUMMARIZE ( ALL ( bi_manufacturer ), bi_manufacturer[Manufacturer] ),
        "Revenue", [Revenue]
    )
VAR FilteredList =
    FILTER (
        ListOfManufacturers,
        RANKX ( ListOfManufacturers, [Revenue],, DESC ) <= TopRank
    )
VAR SelectedManufacturerRevenue =
    CALCULATE ( [Revenue], bi_manufacturer[Manufacturer] = SelectedManufacturer )
VAR UnifiedList =
    UNION (
        FilteredList,
        { ( SelectedManufacturer, SelectedManufacturerRevenue ) }
    )
RETURN
    IF (
        SUMX ( FILTER ( FilteredList, [Manufacturer] = ManufacturerLine ), [Revenue] ) > 0,
        SUMX ( FILTER ( FilteredList, [Manufacturer] = ManufacturerLine ), [Revenue] ),
        SUMX ( FILTER ( UnifiedList, [Manufacturer] = ManufacturerLine ), [Revenue] )
    )

And the results speak for themselves:

Screenshot of the Top N Revenue values corrected. — Top N Revenue working properly

Is it a lot of work to enact? Yes. But is there an opportunity to extract valuable insights from this type of report? Also yes.

Where to go next?

If you want to take this to the next level, consider adding some conditional formatting. Go back and read my article on creating conditional formatting with a column chart. This will allow you to call out your selected manufacturer a lot easier. It is a small adjustment, but it could go a long way to enhancing your report design.

It is worth considering that my queries to achieve this outcome were very simple. Depending on what measures you are comparing, this could become a very cumbersome solution. It is important that you take the time to review your report’s performance and ensure it does not overwhelm your capacity in the Power BI Service. Use the performance analyzer and continue to tune your measures to ensure no issues with rendering this data.

Conclusion

This is a bit of a challenging project. Even while writing this one, I was getting exhausted. It seems simple in practice, but it does require a lot of effort to achieve this desired outcome. I hope if you run into a scenario where you have this requirement that you will find this solution helpful!

So, have you seen anything like this before? Have you run into challenges like this? If so, make sure to share in the comments below!

Enable Self Help Overlays for Power BI Report Viewers

By Dominick Raimato

On January 30, 2024

In Best Practices, Report Design

We can all use a little self help when it comes to Power BI Reports. I often need help with visualizations, formulas, or even our data sources themselves. A little help can go a long way! However, this post is not about that.

As a report creator, how often do you get asked about details around your visualizations? The questions I most frequently receive are focused on definitions of terms, how a metric is calculated, or how a particular visualization is filtered. In fairness, our report consumers do not live inside of our heads. Therefore, they do not know what we were thinking when we designed our reports. However, a little design change in your report can make all the difference. By adding a self help panel, we can eliminate these questions once and for all!

Preparing for self help

To get started, we need to take a screenshot of our report. I will use SnagIt to capture the report canvas only. If you do not have SnagIt or similar tool, you can easily use the Snipping Tool app inside of Windows. SnagIt allows me to be precise with my selection of the report canvas.

Screenshot of our Power BI Report to be used to design our self help overlay. — Screenshot of my report canvas

Once, I have my screenshot, I will open PowerPoint. I know, it seems crazy, but hang with me on this! I will create a new blank presentation, insert a blank slide, and paste my report screenshot on it.

Adding a screenshot of our Power BI Report inside of PowerPoint to prepare our self help overlay. — Adding my report screenshot to a slide in PowerPoint

With our canvas in place, it is time to work some magic in PowerPoint!

Create self help overlay objects

Our first step is to add a shape on top of our screenshot. First, go to the insert ribbon and expand the insert shape menu. Next, select the rectangle shape and cover your entire screenshot. Do not worry that we cannot see the screenshot as we will fix that next.

Inserting a rectangle shape in PowerPoint to serve as our self help overlay base — Inserting a shape inside of PowerPoint

Next, right click on the shape and select format shape. From there, expand the fill menu and change the color to black. Next, adjust the transparency to a level where we can see the canvas clearly, but realize there is something in front of it. In this scenario, 85% seems to be right for this report. Next, you can make adjustments to the border of the shape under the line section. This is a personal preference with building out self help, but I usually leave it enabled.

Formatting the overlay shape in PowerPoint as a base for our self help. — Making my overlay shape transparent

With our overlay in place, it is time to add our self help comments. Go back to the insert ribbon, expand shapes, and select one of the callout boxes at the bottom.

Inserting callout shapes in PowerPoint for the self help overlay. — Inserting callouts for the self help overlay

Keep adding callouts until you are satisfied with your overlay. I recommend erring on the side of too many callouts. However, make sure you still see the report canvas!

Final version of our self help overlay containing five callouts for our Power BI Report. — Report callouts for our self help overlay

With our overlay designed, it is time to add it to our Power BI Report!

Insert self help overlay into you Power BI Report

The first thing we must do is delete the old screenshot from the slide. Right click on your overlay shape and send it to back. Once you have completed that, you can delete the screenshot from the slide.

View of PowerPoint showing the self help overlay objects without the screenshot from Power BI

Self help overlay objects with no screenshot in PowerPoint

Next, we must save our self help objects as an image. Use Control + A to select all objects and Control + G to put them into a single group. After that, we right click on the overlay group and select save as picture. Save this picture to your device so you can access it in Power BI.

Right clicking on the self help overlay group and selecting save as picture so it can be used. — Save self help overlay group as an image in PowerPoint

Next, head back to Power BI, open the insert ribbon, select image, and insert our saved picture. Then, expand the image so it covers the entire report canvas. You will note two things. First, the image is transparent, so we do not hide the report canvas. Second, you will notice that the self help overlay does not reach the edges of the canvas. We can fix that by going to the general section of the format pane, expanding properties, and set padding for all sides to zero.

Inserting a self help overlay image into a Power BI Report canvas and adjusting the padding. — Inserting self help overlay into Power BI

Now that this has been inserted, we just need to add a few bookmarks to make it appear when we want it to be visible!

Expose self help overlay with bookmarks

Now that we have our overlay in place, it is time to hide it. To accomplish this task, we will use bookmarks. I have already written extensively about how to leverage bookmarks in a previous post. If you are not sure how to leverage them, please use that link to learn more. In this article, I am just going to show you how I quickly configure them so the self help overlay will appear and disappear when desired.

I have already added two bookmarks – one to open and one to close the self help overlay. I will simply configure my bookmark to only update the selected visuals I have clicked on and update the slicer. In the image below, I am opening the self help overlay, so it is visible. However, I will just simply hide the visuals for closing the overlay.

Updating the bookmarks to only show the self help overlay when selected and not impact the entire report canvas. — Updating bookmarks for self help overlay

Now that this is in place, I just need to update the actions on my help button and overlay image. To apply the close self help overlay action, I will select overlay image in the the report, go to the format pane and expand action. Once there, I will set the type of action to bookmark and select my close self help overlay bookmark. I will do the same to the open self help button inside of my report footer.

Setting the action properties on our image to use the close self help overlay to hide our help image — Applying the close self help overlay bookmark to our overlay image

Now that you have this in place, you can easily open and close your self help overlay. To test this in Power BI desktop, you will need to hold down the control key to activate the action. However, that will not be required when you publish the report to the Power BI Service.

Opening and closing the self help overlay on the Power BI Report canvas. — Opening and closing the self help overlay

And just like that, we have enabled your viewers to learn more about your report with a single click!

Anything else to consider?

As convenient as the self help overlay is for your viewers, there is an additional burden for you to keep the overlay up to date. I recommend that you save your PowerPoint file for easy access and updates in the future. This will save you time in the future

I always recommend having a checklist of steps that must be completed when publishing content to the Power BI service. If you choose to use a self help overlay, a step must be added to ensure it is still valid. This will prevent any confusion from your report viewers in the future.

Conclusion

This seems like a trivial addition to a report, but I promise it makes life easier. Any chance you have to eliminate the questions coming to you is time well spent. While it is a little design intensive and adds a burden to future updates, it creates a better overall experience for your report viewers. I highly recommend trying it out once or twice in a development environment so you feel comfortable doing it in production.

So, have you ever created a self help overlay? Have you found it helpful? Or are you a viewer who has benefited from an overlay? Tell me about it in the comments below!

Save Space with a Slicer Panel

By Dominick Raimato

On January 22, 2024

In Look and Feel, Report Design

Nothing is worse than a cluttered report canvas. This is one of the most common pieces of feedback I provide to customers on a regular basis. The idea that you can pack as much information into a single view is honestly obnoxious and leads to confusion. If readers have a hard time understanding the canvas, they will not understand the message you are communicating with your report. And the experience is worse if you have half of the canvas covered with slicers!

In addition to the confusion, it has the chance to impact your report performance. As you add visualizations to the Power BI canvas, you will hurt the overall performance of your report. Because each visual must be rendered and associated data queried from the model, it will impact the responsiveness when loading. But a little known secret about Power BI is that if you hide the visualization from the canvas, it does not impact the performance.

This is where slicer panels come in. Instead of having a cluttered canvas full of slicers that is slow to load, we can hide these slicers and activate them when needed. This is a small change to your canvas, but it provides a lasting impact. It only takes a few minutes to implement if you know how to do it.

Example of a cluttered report canvas. — Example of a cluttered report canvas

To resolve this issue, we will simply add a slicer panel to our report canvas. The best part of this is that we will be able to leverage our existing slicers to save us time!

Prepare the Slicer Panel

The first step in preparing the panel is adding a background. Go to the insert ribbon and insert a square shape from the dropdown. Once it appears, resize it and place it on the canvas. Personally, I add the slicer panel on the left side. However, you may choose to have it appear somewhere else on your canvas. My recommendation is to use whatever enhances your report design best is ideal.

Inserting a shape from the Insert ribbon to serve as the slicer panel — Inserting a shape for the slicer panel

Next, we need to add a way to close the panel. Under the insert ribbon, we will expand the button drop down. From here, you can pick the one that works best for you. I personally use the back button, but the reset button seems to work well for others too. You might need to perform some updates to the formatting so it is easily visible on your slicer panel.

Inserting an exit button from the button drop down to help us close the slicer panel. — Inserting an exit button for your slicer panel

Now that we have our panel in place, it is time to add our slicers.

Loading Slicers into our Panel

In our example, we already have our slicers in place. We are going to use the selection pane, found under the view ribbon. From there, we will re-order the items on our canvas so our panel appears above any visualization we add to our report. We also want to make sure our slicers sit on top of our shape so they are not hidden. Once this has been completed, you can arrange your slicers on the shape.

Updating the canvas order and placing the slicers in the right sequence for our slicer panel — Updating our canvas order and arranging our slicers

Next, as a best practice, we will want to rename our shape and our button. The traditional way to perform this task is to update the title under the general settings of the format visualization pane. However, there is an easier way to perform this action! Instead, just double click on the object on the selection pane and you can update it right there.

Renaming objects on the Power BI canvas using the selection pane by double clicking on the object name. — Renaming objects on the Power BI canvas

You will note that I have also grouped these objects together by selecting them, right-clicking, and selecting group. I renamed the group the same way I did with the other objects. This is not required, but makes life a lot easier for the next step.

Show and Hide your Slicer Panel

Now that we have our panel in place, it is time to hide it. To accomplish this task, we will use bookmarks. I have already written extensively about how to leverage bookmarks in a previous post. If you are not sure how to leverage them, please use that link to learn more. In this article, I am just going to show you how I quickly configure them so the panel will appear and disappear when desired.

I have already added two bookmarks – one to open and one to close the slicer panel. I will simply configure my bookmark to only update the selected visuals I have clicked on and update the slicer. In the image below, I am opening the slicer panel, so it is visible. However, I will just simply hide the visuals for closing the panel.

Configuring bookmarks to only update the selected visuals ensuring that only the slicer panel will be affected by the opening and closing of my report. — Updating bookmarks to open and close the slicer panel

Now that this is in place, I just need to update the actions on my buttons. To apply the close slicer panel action, I will select by button in the slicer panel, go to the format pane and expand action. Once there, I will set the type of action to bookmark and select my Close Slicer Panel bookmark. I will do the same to the open slicer button inside of my report footer.

Adding the bookmark action to the button to close the slicer panel — Applying the close slicer panel action to the close slicer panel button

Now that you have this in place, you can easily open and close your slicer panel. To test this in Power BI desktop, you will need to hold down the control key to activate the action. However, that will not be required when you publish the report to the Power BI Service.

Short video of the slicer panel opening and closing in Power BI — Opening and closing the slicer panel

Just like that, we have provided more space on our report canvas and made it easier to view!

Anything else to consider?

If you remember, I put my slicers in a group with the panel objects. While not required, it is a best practice. The reason for this is that you will at some point need to add a new slicer to your report. To make it easier on yourself, the group allows you to update the objects without having to update your bookmarks.

When I am in a hurry, I feel like bookmarks never update the way they are supposed to. The result is in a rushed experience that never works out. However, because I used groups, I can simply drag my slicer into the group and not have to make any adjustments to the bookmark. It is an extra step in the process, but it will save you considerable time and headaches in the future!

Conclusion

This is a small update for a report, but it really makes an impact. I know it takes extra time, but I promise you that it is worth the effort to preserve your canvas. This is especially helpful if you have reports using more than five slicers for a page.

Have you used a slicer panel before? Do you find them helpful? If so, tell me in the comments below!

New Feature – Simplify Reports with Field Parameters

By Dominick Raimato

On May 19, 2022

In Field Parameters, Report Design

Did you check out the May 2022 Power BI update? I am genuinely excited about this release! So many great features to check out. However, the standout favorite from this release is the introduction of field parameters. I started playing with it and quickly discovered it is possibly my favorite release this year!

To set the scene, let me give you a common scenario that I encounter. Everyone wants to sort and view their data their way. With various dimensions and measures, there are endless possibilities when it comes to designing your report. Let’s say I have three categories that I can use against six measures. I could end up creating 18 different visualizations to handle all of these combinations. But with field parameters, I can simplify that process!

Enable the Preview

At the time I am writing this article, field parameters are a preview feature. You need to enable the feature first in order to use it. Go to the files menu, select options and setting, and click on options. From there, you need to go to the preview features section and enable field parameters.

Enabling the field parameters preview feature

Finish enabling the feature by restarting Power BI. As a result you will be ready to use field parameters in your report!

Creating Field Parameters

If you follow my blog, you will find the process to create field parameters similar to what if parameters. To create a field parameter, go to the modeling tab and select field under the new parameter menu.

Next, we give our parameter a name. To keep things basic, I am naming mine dimensions. Then I add any fields I want to use as a parameter. I set my order and click create when I am ready.

Two new things appear. The most obvious is a slicer on the report canvas. But there is also a new table that appears in the field list. We will be using that with our visual in a moment.

With the field parameter in place, it is time to apply it to a visual. Using a simple column chart, I will put my parameter on the X Axis. Then I will add my revenue measure to the Y Axis. With this in place, I can quickly jump between my parameters.

How cool is that?! We took one visual and made it easy to pivot around with different dimensions. Talk about providing context while managing your report canvas!

Do Measures Work As Well?

You bet! Similar concept applies here. You create a new field parameter like above and add measures. A new slicer is added. Once in place, I add it to my Y Axis and start manipulating my chart.

And if you had a keen eye, you also might be wondering if you can do multi-select on the slicer. And if you guessed yes, then you were right!

Selecting multiple parameters on a chart

And if you wanted to go a step further, you could apply the same principle to a table or matrix visual too.

Done well, you could essentially create a simple pivot table inside of Power BI that your users can manipulate with a few clicks.

The Use Case for Field Parameters

Plain and simple, the use case for field parameters is enabling you to scale visualizations with minimal effort. Based upon our three dimensions and six measures, we would need to find space for all of these charts. This solution simplifies the need to create space for all of these options.

Even without this feature, we could make something like this work. A couple of weeks ago I wrote an article on using bookmarks to accomplish this task. It works well, but field parameters make it faster and easier to maintain. If you need to make changes, you might spend additional time updating each individual visual.

You also could allow users to customize visuals in the Power BI Service. They could accomplish the same goal on their own, but there is one problem – most people don’t know how to do it! Pair that with an unfamiliar data model and it can become a recipe for disaster. This solution guides the user to use the right fields to simplify their experience.

Anything Else to Consider?

I think the only major thing left to think about is your slicer behavior. My measures were pretty similar, so it would be okay to allow the multi-select behavior. However, if I had a measure calculating a percentage, I might need to enforce a single select to avoid issues with the scale of values.

Personally, this is one of those simple enhancements that is really going to make an impact for me. How about you? Are you going to try out field parameters in your reports? Tell me in the comments below!

Tooltips – Adding Context for Better Understanding

By Dominick Raimato

On May 12, 2022

In Look and Feel, Report Design, Tooltips

Tooltips are hands down one of the most important elements to your report design. When asked what I think is important about reporting, I often say you must find “The Why”. Additional context is critical to making the right decisions with data. This is where tooltips step in to provide better context with visualizations.

Now tooltips are already part of report visualizations. In fact, any measures you place on the report canvas often show up as a tooltip. But I am going a step further with this article on how to create rich tooltips that drive a better experience.

The Basics of Tooltips

The concept of tooltips are basic – we add additional measures and information to a pop-up window when we hover over a data point. These data points are generally not complex, but can add additional information. In addition, it reduces clutter on the canvas by keeping the visualizations clean.

Let’s start with charts I created in the article where I show how to add conditional formatting to column charts. Instead of creating a complicated chart, we will add context with tooltips.

Chart Showing Monthly Revenue Versus Goal

From the conditional formatting used, we can see plainly see that we missed our goal in February and March. We are just a few days into May, so we still have time to reach our target. When I hover over a bar, I can see the specific current year revenue.

Hovering to Discover Current Year Revenue in March 2022

When I select the column chart visual, I have the opportunity to include additional measures with the tooltips section. I just simply drag my CY Goal and PY Revenue measures into the tooltips section to make them visible when I hover over the visual.

This simple addition now provides additional context to our visualizations and makes it easier to understand. We can see that we were $200,000 short of our goal in March. While not ideal, we can easily see the gap. And while it is not the prettiest, it is functional.

Next Level Tooltips

Maybe you want to add a number of measures at once to your tooltip. Or maybe you want to add some conditional formatting. Or maybe you just want to add a little style to make them look nicer. Regardless of your reason, you can create your own custom tooltips in Power BI. It takes a little effort, but it really add value to any report you create.

One of the first steps in creating custom tooltips is considering what you want to display. This is critical because you want to ensure the tooltip doesn’t become an additional report page. You also might run into slowdowns if there are too many calculations on your tooltip. Remember that less is more when it comes to quality visualizations. Once we have our plan in place, it is time to build out our custom tooltip!

Step 1 – Preparing the Canvas

The first thing we need to do is create a new page in our report. Give the report a meaningful name and then hide it from regular view by right clicking and selecting hide page. This prevents the page from being viewed when not used as a tooltip.

Next, we need to do setup on the page. On the visualization pane, we need to open the formatting section. The first thing we need to do is expand the page information section and enable the allow use as a tooltip option.

We also want to adjust the canvas size. By default, our tooltip page size matches the default of the report. That will be way too large! I expand the canvas settings section and select the tooltip page type. While I have the option to create a custom size page, I try to use the pre-defined one to avoid overload.

With all of this in place, we are ready to move on to designing the tooltip.

Step 2 – Build Out the Tooltip

This part can be a little tricky. Because tooltips are dynamic, you will run into issues where your measures might be all over the place. To make your life easier, I recommend setting a page level filter to help design and validate your data.

Next, you want to build out your tooltip measures. I have selected only four values for the sake of simplicity. If you wanted to get adventurous, you could add a chart or an embedded image. However, I have something I want to call out and this simple view will assist.

Once you are happy with your design, just simply remove the filters from your page!

Step 3 – Apply Your Tooltip

Now that we have our custom tooltip, we just need to apply it to the visual. Select your visual and go to the formatting pane. Then select the general tab and expand the tooltip section. From there, double check that report page is selected as the type and pick your custom tooltip. Is your page missing? If so, go back to step 1 and make sure your page settings are correct!

With that in place, you can hover to discover your context! If your tooltip shows blank values, go back to your custom tooltip page and make sure the page level filters are cleared.

And just like that you have applied your own custom tooltip to a report! You can get as fancy as you wish with your tooltips. Just keep in mind that too much can be cluttered. Here is an example of a more complex solution I did with the Dunder Mifflin Sales Database:

Jim Halpert’s Flirting with Pam has Resulted a Drop in Sales Year over Year

You can see that I was able to add some context to a simple revenue report. I could show that even though Jim’s year over year sales are down, he is trending upward with his sales. I even added his picture to make it easier to identify him.

Regardless of what you do, make sure you keep it clean and simple to make your tooltips effective!

Added Bonus to Custom Tooltips

All of this effort provides an additional benefit that you might not notice right away. When it comes to report performance, your custom tooltips might help with the loading of your report canvas.

The best way to see the clear difference is with the underlying code. I turned on the performance analyzer and pulled the query for the original chart with the standard tooltip versus the custom tooltip.

//Standard Tooltip
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'LocalDateTable_112eb4ab-6627-45e0-a348-6394cb37748e'[Month],
      'LocalDateTable_112eb4ab-6627-45e0-a348-6394cb37748e'[MonthNo],
      __DS0FilterTable,
      __DS0FilterTable2,
      "CY_Revenue", 'SalesAggregated'[CY Revenue],
      "CY_Goal", IGNORE('SalesAggregated'[CY Goal]),
      "YoY_Revenue_Change", IGNORE('SalesAggregated'[YoY Revenue Change]),
      "v__to_Goal", IGNORE('SalesAggregated'[% to Goal]),
      "CY_Revenue_Color", IGNORE('SalesAggregated'[CY Revenue Color])
    )

//Custom Tooltip
  VAR __DS0Core = 
    SUMMARIZECOLUMNS(
      'LocalDateTable_112eb4ab-6627-45e0-a348-6394cb37748e'[Month],
      'LocalDateTable_112eb4ab-6627-45e0-a348-6394cb37748e'[MonthNo],
      __DS0FilterTable,
      __DS0FilterTable2,
      "CY_Revenue", 'SalesAggregated'[CY Revenue],
      "CY_Revenue_Color", IGNORE('SalesAggregated'[CY Revenue Color])
    )

The obvious difference between the two code blocks is the custom tooltip does not load all of the measures when the page is rendered. What does that mean for your report? Simple – you can speed up the loading of the report page by pushing some of your measures to a custom tooltip! This is great for complex measures that might provide context, but are not always needed when viewing the report.

Keep in mind that if your measures are slow, it will hurt the performance of your tooltip!

Anything Else I Should Know?

Probably the most important thing to know is that sometimes these tooltips can be frustrating. They take practice and sometimes a little extra effort. But the more you do it, the easier it will become. Just keep working at it and you will be a pro in no time!

Have you made a custom tooltip before? Have you built anything adventurous? Any favorite use cases? If so, 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!