Author: Dominick Raimato Page 2 of 4

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!

Microsoft 365 Group to Drive Row Level Security (RLS)?

By Dominick Raimato

On July 21, 2022

In Governance, Row Level Security

As you are reading this post, you are one of two people. The first group are saying “Wow – I never thought to use existing Microsoft 365 Groups to drive my Row Level Security!” The other group is screaming “Microsoft 365 Groups don’t work for Row Level Security!”

Technically, the second group is correct. If you try to apply a Microsoft 365 Group for Row Level Security, you will discover that it is not available. This is very frustrating for a number of report creators. But as with all the great solutions, necessity is the mother of invention.

The Scenario

In the last month, I have been approached by two customers about how to leverage existing Microsoft 365 Groups in Power BI. When talking to these customers, they built out their security model using these groups. With Microsoft heavily relying on groups to drive security, it throws a wrench in things. And unlike SharePoint which allows you to leverage existing Active Directory groups, Teams does not allow you to do that.

So you bought into Teams and started using it to allow self service collaboration. Your users are successfully updating memberships with no issues. But now you need to create a security group and replicate it. Doing it manually takes time and it is frustrating at best. Plus it will likely lead to multiple service desk calls when it is not working as planned.

And while I am not lazy, I like to make simple solutions that enable users. So let’s automate the solution to make it easier!

Power Automate to the Rescue

Power BI and Power Automate are part of the Microsoft Power Platform. While powerful alone, they are better together when it comes to creating solutions. Power Automate enables end users create self-service automation solutions. We can quickly build a flow that will automate our replication process.

The concept is simple – we will create a flow that monitors a Microsoft 365 Group membership and update our security group. You might have looked into Power Automate in the past and decided to not use it because of licensing costs. But the best part this solution is that you can use the free tier of Power BI to enable the solution!

Implementing Microsoft 365 Group Replication

We could create the flow from scratch, but why do that when I have already built it! This will help you save some time and quickly re-deploy it for other groups.

Step 1 – Create a Security Group

To get started, you must create a security group that you will use. Since I am 100% cloud based, I go to the Microsoft 365 Admin Center to create the group. Go to Teams and Groups on the left hand navigation and select Active Teams and Groups. From there, create a new group. Select the Security group type and click next. Give your group a name and finish up the group creation.

Once created, go ahead and open the group. Make sure whatever account is creating the flow in Power Automate is added as the group owner. Lastly, before you close the window, copy the group ID out of the URL as you will need it shortly. You can accomplish the exact same task in Azure Active Directory, but I feel like this is easier.

Finalize the setup of your Security Group

As a point of convenience, you might want to add the existing members of the Microsoft 365 Group into the security group. This will need to happen anyway, so why not do it now?

One last thing – if the account creating the flow is not already a member of the Microsoft 365 Group, make sure they are added before moving on to the next step!

Step 2 – Import the Flow

First, download the prebuilt package I created. This will save us some time with the deployment of this solution and will make it easier for you to replicate.

One you have downloaded the package, login to Power Automate using your Microsoft 365 Credentials. Once signed in, go to My Flows on the left, select Import, and pick Import Package. Click upload, pick the package you downloaded, and click OK.

Once you have loaded the package, you need to update the connection details for the Microsoft 365 Groups connector and the Azure AD connector. Click on Select during import and specify which account to use. Click Import when you are finished

Setting up connections when importing the flow

The import will take a few moments. You will receive a message when it is finished. Once the import is complete, click on Open flow.

Import complete message

Next, we need to update the flow to use the groups you specify.

Step 3 – Specify the Microsoft 365 Group and Security Group

Updating the Microsoft 365 Group is really easy. Just clear out the existing content and select your group from the drop down. It is easy as that!

For the security group, it is a little harder. All you need to do to is add the group id you copied when you created the group in our variable. Just set the value field for Initialize Security Group ID Variable block. We are assigning it to a variable because we need to use it with two actions. I don’t have to do it this way, but I like it because I only have to update it once. This way I don’t miss anything!

Make sure you save your work before moving on to the next step!

Step 4 – Test Your Flow

Now that everything is in place, go ahead and test your flow. Add a new user to a Microsoft 365 Group. It will take a minute or two, but it should appear in the security group.

Once it shows up, you can remove the user from the Microsoft 365 Group. Again, it will take a minute or two, but it should be removed from the security group.

If it passes the test, you are all set! Congratulations! Go and use the security group as appropriate in Power BI!

Final Thoughts

This is a simple solution, but keep in mind this is a very specific solution for a very specific problem. I only use this solution for a very targeted set of groups. You essentially need to duplicate these groups and create a flow for each and every one of the groups you need to replicate. That can get messy really quickly. Personally, I would limit it to less than 10 groups, but you can decide what is right for you.

I would also consider putting this flow under a service account. If the person who sets up the flow uses their account, the flow will stop working when their account is disabled. A service account prevents any issues with this situation.

Have you run into this challenge? Do you think this solution works well? Tell me in the comments below!

Time Zone Management in Power Query

By Dominick Raimato

On June 16, 2022

In Power Query

I ran into a unique challenge this past week when it came to managing time zones in Power Query. I have a dataset that brings a dataset in using Universal Coordinated Time (UTC). Like a majority of the world, my key stakeholders do not reside in the UTC time zone. On top of that, they wanted to use multiple time zones, so this could become quite complicated.

You might be reading this article and asking why would this be such a complex issue? Well there are a couple of factors that you need to consider. Naturally you need to consider the time zone you need to use. In addition, you must consider daylight savings time. In the United States, every state except Arizona observes daylight savings time. As a result, I have to adjust my model accordingly. And while 99% of my work is based in the United States, many other countries across the world observe Daylight Savings Time.

To add to the complication, one of the required time zones was for Indian Standard Time (IST). If you are not familiar with the IST time zone, there is an additional 30 minute offset from UTC. In short, if it is 1:00 AM UTC, then it is 6:30 AM IST (UTC +5:30).

Since I know I am not the only person with this challenge, I thought it would be helpful to share my experience and how you might be able to work around this issue.

Convert UTC to IST Time Zone

Let’s start with an easy conversion. When setting the IST time zone, you do not have to worry about daylight savings time. This means it is an easy conversion!

To get started, we are going to create a custom column in Power Query and convert our date/time in the UTC time zone. However, the dataset does not assign the time zone to the timestamp. We will assign it using the DateTime.AddZone() function. The function accepts three parameters:

Date/Time value (in our case, the Timestamp column)
Hour offset
Minute offset

=DateTime.AddZone([Timestamp], 0, 0)

This formula will replicate your current timestamp and add “+00:00” at the end to show no offset from UTC. Now that we have a time zone assigned, we need to convert to IST. We will wrap our last function with the DateTimeZone.SwitchZone() function to adjust the offset. The parameters are the same as the function above.

=DateTimeZone.SwitchZone(DateTime.AddZone([Timestamp], 0, 0), 5, 30)

Now the column has been updated with “+05:30” at the end, but the time has been incremented by the same amount. It is that easy!

Now, you might not want the time zone offset showing in the report canvas. We can easily remove that from the view by wrapping the last formula with DateTimeZone.RemoveZone()

=DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTime.AddZone([Timestamp], 0, 0), 5, 30))

Now there is just one pesky problem left – our new column is shown as a string instead of date/time. This is an easy fix! Just crack open your advanced editor and add the column type on the same step!

Custom Column = Table.AddColumn(#"Changed Type", "TimestampIST", each DateTimeZone.RemoveZone(DateTimeZone.SwitchZone(DateTime.AddZone([Timestamp], 0, 0), 5, 30)), type datetime)

Yes, you can update the column type with the next step. But I am a fan of keeping things compact and clean. Since you are already doing the transformation, it is an easy add to save some compute time!

If your time zone is not IST, you can look up the offset here. You just need to update the code above with the correct offset.

What About Daylight Savings Time?

The biggest challenge is to know what rules define the time shift. In the United States, our formula is simple. Daylight Savings Time starts on the second Sunday in March and ends on the first Sunday in November. So how do we put that knowledge to work?

One option is to add a field on your date dimension to show if daylight savings time applies. That works, but it requires maintenance long term. Another option is to create a table of dates, but once again it is a lot of work to keep current.

But there is a way to scale this logic using a few functions in Power Query

Find the Start and End of Daylight Savings Time

Like before, let’s start off easy by finding the end of Daylight Savings Time. The first thing we need to do is establish the first day of November. We will use the #date(year, month, day) function to set the date. But remember, our goal is to make it scalable, so we need to make the year dynamic:

=#date(Date.Year([Timestamp]), 11, 1)

Now that we have the first day of the month established, we will use it to find the first Sunday of the month. We will use the Date.EndOfWeek() function to establish the last day. We will add a parameter with the value of “1” to shift the end of the week from Saturday to Sunday.

=Date.EndOfWeek(#date(Date.Year([Timestamp]), 11, 1), 1)

Now to find the second Sunday of March, we will use the same formula as a base. We will change the 11 to 3 for March. But there is one difference, we are wrapping this formula in Date.AddDays(). We will use that formula to add 7 days to our date which will give us the second Sunday in March.

=Date.AddDays(Date.EndOfWeek(#date(Date.Year([Timestamp]), 3, 1), 1), 7)

Like I said above, your time zone might have a different set of rules. However, with some research, I found that the rules are generally the same. You will need to add or remove weeks to get to your desired date. Maybe use the End of the Month and work backwards. If it starts on Saturday, you can ignore the extra parameter. And if you are in one of the countries that observes on a Friday, just use the Date.AddDays() and work forward/back the appropriate number of days to make it work!

Apply Dates to Time Zone Offset

Now that we have the start and end of Daylight Savings Time, we just need a basic conditional statement to provide the offset. Because our start and end dates are pure dates with no time associated, we will need to do our comparison using the DateTime.Date() function to convert our timestamp.

=if DateTime.Date([Timestamp]) >= Date.AddDays(Date.EndOfWeek(#date(Date.Year([Timestamp]), 3, 1), 1), 7) and DateTime.Date([Timestamp]) < Date.EndOfWeek(#date(Date.Year([Timestamp]), 11, 1), 1)

This will check our timestamp and make sure it is between our two dates. Now we just need to use the same logic from above to show the offset:

=DateTimeZone.RemoveZone(if DateTime.Date([Timestamp]) >= Date.AddDays(Date.EndOfWeek(#date(Date.Year([Timestamp]), 3, 1), 1), 7) and DateTime.Date([Timestamp]) < Date.EndOfWeek(#date(Date.Year([Timestamp]), 11, 1), 1) then DateTimeZone.SwitchZone(DateTime.AddZone([Timestamp], 0, 0), -4, 0) else DateTimeZone.SwitchZone(DateTime.AddZone([Timestamp], 0, 0), -5, 0))

Since I am using Eastern Time in the US, there is an offset of 5 hours from UTC, but only 4 during Daylight Savings Time. With this all in place, we now have a function that will scale with Daylight Savings Time over the years. Feels like a lot of work to get here, but it works well!

Get Fancy with a Time Zone Function

Now, you might be thinking that this could get painful if you need to provide multiple time zones over and over again. For those of us in the United States, we are using the same logic for several time zones. So instead of doing everything manually, you can create a function to make your life easier!

Using the same logic, the function will accept two parameters: the timestamp and the offset. We then can use a few lines of code and make a function that we can reuse over and over:

(TimestampValue as datetime, TimeOffset as number) =>
let
    TimestampDate = DateTime.Date(TimestampValue),
    TimestampYear = Date.Year(TimestampValue),
    Source = DateTimeZone.RemoveZone( if TimestampDate >= Date.AddDays(Date.EndOfWeek(#date(TimestampYear, 3, 1), 1), 7) and TimestampDate < Date.EndOfWeek(#date(TimestampYear, 11, 1), 1) then DateTimeZone.SwitchZone(DateTime.AddZone(TimestampValue, 0, 0), TimeOffset + 1, 0) else DateTimeZone.SwitchZone(DateTime.AddZone(TimestampValue, 0, 0), TimeOffset, 0))
in
    Source

With a few substitutions, I can simplify the logic and make it easier to understand. Now I can just invoke the function by using the timestamp and adding the offset. Not too shabby!

Anything Else to Consider?

This solution is just using dates and ignores the official time when the change takes place. In my use case, the precision was not that important when we are looking at 4 hours out of 8,760 hours in a year. If you need that level of precision, you will need to add time to the logic in the formulas.

I will say, this is a gaping hole in Power BI. With Power Automate, you are able to perform precision conversions based upon time zones much easier. A majority of my work just uses dates and ignores time, but there are use cases like audit data that we need this information. Hopefully Microsoft will add functionality here one day. However, users have remarked about this challenge for years. I might be better off waiting for the United States to pass a law eliminating Daylight Savings Time. But that is not looking too good either…

Anyway, how have you handled this in the past? Do you have a different approach? If so, tell me in the comments below!

Microsoft Build 2022 – What You Need to Know

By Dominick Raimato

On June 9, 2022

In New Feature Alert

If you are like me, you are still in awe about some of the major announcements made at Microsoft Build. I am excited about what is happening with Power BI in the near future and you should be too. With all of this news, people have been asking me what my favorite announcements are. I decided it was a good idea to talk about these new features and share why I think you should be excited as well!

Datamarts in Power BI

This is the headline that stole the show. We have been waiting for this solution and everyone is excited that it has arrived. In short, datamarts provide quick access to transformed data for rapid report development for citizen developers.

What makes this significant?

You might already be asking “Why do I need to care about this? I can do that already with datasets and dataflows!” We always are talking about the right tool for the job. Datamarts can provide quality data for users with a few clicks. But what makes it special is the ability to transform and build your model 100% in a browser. While you may think this is not a big deal, it actually opens up a whole group of users who have not been able to build advanced reports. While I am a Microsoft person, I still have a soft spot for Linux. And in reality, I know there are a chunk of users who love MacOS or Chrome OS. Because of this change, those users now can build out advanced reports without having to use a Windows VM to accomplish the goal.

Also, what I love about this feature is the ability to enable smaller groups within an organization to create mini data warehouses. While I am a firm believer you should engage your data teams to get assistance with your raw data, this feature enables power users to create their own solutions for lower priority initiatives. It does not replace your current strategy, but can help extend it for business units expanding their horizons.

Anything else to consider?

Keep in mind that datamarts can only be created in premium workspaces. Premium Capacity and Premium Per User both can be leveraged, but you will need to upgrade your pro workspaces. If you have not revisited your licensing strategy, it might be time to review it and make some adjustments.

You can learn more about datamarts here.

Storytelling in PowerPoint

When I used to work at The Hershey Company, a consultant once asked me if we were given a special version of Microsoft Office that only had Outlook and PowerPoint. Like Hershey, many companies seem to solely rely on PowerPoint to get their message across. For those organizations, the solution was to pull screenshots or export pages into their decks. But storytelling in PowerPoint changes the game.

What makes this significant?

My favorite feature with Power BI is the ability to manipulate the data on the screen during a presentation. By bringing the report into PowerPoint, you now have the best of both worlds – refined presentations paired with interactive reports.

In addition to interactivity, I love the ability to get data in real time with my reports. Everyone has weekly/monthly/quarterly meetings where they use the same template over and over again. Since you are integrating the live report into PowerPoint, you have one less slide to worry about!

Anything else to consider?

There are two main things to consider when it comes to using Power BI inside of PowerPoint.

The first one might be a blocker with your organization. You will need to install the Microsoft Power BI add-in to PowerPoint. If your organization blocks add-ins, you might not be able to use this feature. I suggest searching for this feature and submitting a request ASAP if you feel this add-in would be helpful. This way it is ready for you when you need it.

The other is permissions to the report. If you do not freeze the report, you will need to ensure recipients of the report have access to the report. Much like embedding your reports in Microsoft Teams or SharePoint, you need to grant access to the underlying content.

You can learn more about storytelling in PowerPoint here.

Express Design with Power Apps

“Wait a minute – you are talking about Power Apps? I thought this was all about Power BI?!” I know, but trust me when I say it all connects!

I recently started using Power Apps in Power BI for very specific use cases. I have experience with Power Apps, so it is not a big deal for me to quickly build one to integrate with my reports. However, if you are new to Power Apps, this feature might be a shortcut to getting started.

What makes this significant?

There are several different reasons to use Power Apps inside of Power BI. One of the easiest scenarios you might use Power Apps is for writing data back to your database. If you want to enable report consumers the ability to add records, you can embed an app that handles the transaction.

You could sketch up a form on a whiteboard or a piece of paper. Once complete, take a picture of it and load it into Power Apps. Then map the fields to your fields and you will be set. Talk about a true no code app design experience!

Anything else to consider?

The biggest consideration for your solution is licensing required for Power Apps. If you are writing back to SQL or Dataverse, you will need to license your app users or the app itself to use premium connectors. This adds additional cost to your solutions if you have not already purchased licensing.

You can learn more about express design here.

Additional Microsoft Build Announcements

While the three announcements above are my favorite, there are some honorable mentions as well. They do not have me as excited, but they are worth noting.

General Availability of Metrics in the Power BI Service

Originally named Goals, Metrics allows you to see your key performance indicators (KPIs) in a single scorecard. This feature has been around for a while in preview, but is finally entering general availability. You can read more about metrics here.

Power BI is coming to Outlook

I will be honest – I am not a fan of email. With Microsoft Teams, I do not feel the need to use Outlook like I have in the in the past. I really like how Microsoft has integrated Power BI inside Teams through channels and the app. This could provide a little easier passing of reports to consumers if you are heavily reliant on email in your role. You can read about Power BI inside of Outlook here.

Microsoft Build 2022 Was So Exciting!

I always enjoy hearing about new features in Power BI. It felt like Christmas came early this year with Microsoft Build providing these impactful announcements. I feel like these are announcements that only happen during Ignite.

How about you? Did you find Microsoft Build just as exciting as I did? Did you hear about anything else that will revolutionize your use of Power BI? If so, tell me in the comments below!

Get a Strategy – Power BI Licensing

By Dominick Raimato

On May 26, 2022

In Licensing

What licenses do I need for Power BI? When I first start working with a customer, this is usually the first question I am asked. Power BI licensing is somewhat complicated. Most people tend to focus on the price, but they forget to focus on the actual solution and goals of the organization. As a consultant, I prefer to focus on the goals of the organization and size the licensing appropriately. A single feature might not justify Power BI Premium Capacity. However, a few chained together could provide a real return on your investment.

My goal is to enable you to make the right choices when it comes to Power BI licensing to meet your budget. No one wants to buy more licensing than they need. While I cannot cover every single situation, I will address the most common licensing scenarios for content creators, modifiers, and consumers.

Power BI Licensing Options

This is where everyone wants to start. They have read the articles and have their questions. Before we dig into which one is right for you, let’s start with the different options that are out there.

Power BI Free

Power BI Free allows users to only work within their personal workspace. Unless they have been granted access to a workspace using Power BI Capacity, they will never be able to consume content outside of their personal workspace.

While restrictive, you do have the potential to try reports out and build some custom visualizations with no additional cost. You might find it to be a great place to start your journey while controlling costs.

Power BI Pro

The first paid tier of Power BI Licensing is Power BI Pro. This license allows for users to interact with workspaces inside of the Power BI Service. Regardless of the role, you will need this license to work outside of their personal workspace if the organization has no Power BI Premium Capacity assigned.

Often people think they can bypass this license, but find out how they need it. You can purchase this license independently as an add-on. However, it is included with the top tier of enterprise (E5), education (A5), and government (G5) licensing packs. At the very least, you must plan for Power BI Pro licenses for your content creators and modifiers as I will explain later.

Power BI Premium Per User (PPU)

The next tier is licensing users with Premium Per User. This intermediate step allows for the features found in Power BI Premium Capacity at a reasonable price point. This is a great option for small organizations who need premium features but are trying to manage their budgets. The biggest catch with Premium Per User is that if the workspace has been designated for Premium Per User features, consumers must have a Premium Per User License. However, content published to a standard workspace can be viewed by other Power BI Pro users.

A little known fact is that there is a step-up license option available for users who already have a Power BI Pro license. It essentially doubles the Power BI Pro license price for each user, but saves you from having to pay for both.

Power BI Premium Capacity

The last licensing tier is to allow users to license a workspace using Power BI Premium Capacity. Instead of licensing users in the entire organization for Power BI, you license a workspace to use Premium Capacity. This opens up content to all users from your organization to access reports without a paid license.

There is a catch – content creators and modifiers still need to be licensed to manage the content in a workspace. So if they do not have a Pro or PPU license, they will not be able to create or manage content. But the biggest benefit is that Pro users will be able to leverage premium features in the workspace without having to upgrade their license to PPU.

Premium Capacity licenses the service and eliminates the need for individual licenses for consumers. It costs more up front, but can pay off considerably.

Premium Per User or Premium Capacity?

So you have already looked at the Power BI Pricing and Feature Guide and determined you need premium features. Which option do I choose? Focusing on the end solution, I find a few factors that push you one direction or another.

Compliance and Regulatory Concerns

For some deployments, you have to use Premium Capacity regardless of other considerations. Premium Capacity has two features that are only available with this licensing option.

Multi-Geo Deployment Management

Common with highly regulated data, Power BI Premium Capacity allows organizations to store their data in a region outside of their home tenant to meet regulatory requirements. Here is an example:

Contoso Financial is based in the United States with locations across the globe. The Berlin, Germany office wants to do some analysis on regulated data that must be stored within the boundaries of their country to remain compliant based on GDPR laws. Because Contoso Financial is based in the United States, their Power BI tenant stores all of the data in the same location. To get around this, they must purchase a Power BI Premium Capacity and assign it to a region in the EU to satisfy regulatory requirements.

This feature is only available for Premium Capacity and must be used to meet these requirements.

Bring Your Own Key (BYOK)

Similar to what was outlined with Multi-Geo Deployments, some organizations need to maintain their own keys for regulatory purposes. If an organization wants to protect select data with their own key, they must use a Premium Capacity so they can assign their own key to satisfy these needs.

Data Model Size

Possibly less of a concern when just starting your Power BI journey, but data model size can play a role in your decision. With Power BI Pro, your data model is limited to 1 GB in size. Power BI allows you to increase that limit. Premium Per User increases the limit to 100 GB model size while Premium Capacity lifts it to 400 GB.

I always recommend providing quality filtering when building your data model. This allows you to keep your model lean and speeds up refreshes. It is possible to prevent the need to move to Premium licensing with a little query tuning and patience.

Consumer Audience Size

The last thing I consider when picking between Premium Per User and Premium Capacity is the size of the consumer audience. There is a tipping point where it is cheaper to use Premium Capacity as opposed to individual Power BI licenses.

If you have over 500 users who are using Power BI Pro just to view content, you will save money by deploying Premium Capacity. Using the same logic, the tipping point for Premium Per User is 250 consumers.

The biggest thing is to remember to exclude your content creators from this count. They still need a Power BI pro license to publish content!

Power BI Licensing and External Sharing

The last big question is how do I share my content with external users? There are a few options available, so it is critical to find the right choice.

Adding Guest Users

This is the easiest approach. Adding a user as a guest using Azure B2B takes a few clicks to complete. If they are accessing content in a premium capacity workspace, then your work is finished! However, if they need a license, there are two options for you.

The first option is the user brings their own Power BI license. If they have one from their own organization, it will allow them to view content in your tenant. This is great if you are inviting users from an organization that is using Power BI already.

If they do not have a license, you can simply apply one of your licenses to the guest user. Obviously there is additional cost to your organization, but for a few users it could work well.

Create a User

I am not a big fan of creating a new user in your organization. There is an additional cost to license the user and you have to maintain their account. You can do this if you want, but I feel like it is a lot of effort. Make sure you create a clear line of communication with your guest organization. As people leave the organization, their account in your tenant will not be automatically disabled. This could be a concern depending on the level of sensitivity with the underlying data.

Embed Your Power BI Content

The best way to share your content with a large audience is through Power BI Embed. If you have Power BI Premium Capacity, it is already included in your license. Using a service principal, you can authenticate and display content from Power BI inside a website. If you already have a customer portal, you can embed your report to keep it secure and easy to view.

Sometimes you might be teetering between Per User and Capacity licensing and this pushes capacity across the line. It is not much of a stretch price wise and brings a lot of additional capability.

But what if you don’t have Power BI Premium Capacity or need it? There is a little secret to enabling that external sharing at a lower price point. The Power BI Embedded service allows you to share content at a lower price point. Even better is that you can turn it on and off in the Azure portal. If you are developing your solution, you can turn it off when you are not using it to save money until you are ready to deploy it.

Bottom Line with Power BI Licensing

It is as simple as this – take some time and think through your scenarios before purchasing your Power BI Licensing. There are a lot of options and angles to consider. Keep your future goals in mind as you plan your purchase. I see customers either under spend which results in grief. Or they overspend to just get things done. Taking the time to size your environment right will pay off handsomely over time.

Did I miss any scenarios? Any questions you might have about Power BI Licensing? Did I miss anything? 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!

Bookmarks – A Versatile Feature That You Are Not Using

By Dominick Raimato

On April 28, 2022

In Report Design

Bookmarks are one of those tools that I never really understood on day one. I just kept ignoring them thinking they were not that important. What a mistake!

Bookmarks serve multiple purposes in Power BI Reports. Regardless of your skill level or design experience, they can help any creator. The trick is understanding how they work so you can leverage them.

The Basics of Bookmarks

Sometimes the most basic solutions are some of the best. Bookmarks personify that point. The easiest way to showcase the basics of bookmarks is to save a view you like to use.

When digging into a report, I frequently apply different filters and slicers to find insights. I often find something interesting and will want to reference it later. Before bookmarks, I would have to take some notes about how to reproduce what I found. Well, I should take notes but often forget. Sound familiar? Instead of taking all of those notes, just apply a bookmark!

Creating Bookmarks

Last year, I created a property assessment report for my town. I built the tool to check the assessment value of houses in town and compare them. The reason? My property taxes are directly tied to the assessed value of my property. Like most people, I checked out other properties in town.

When I found one that was interesting, the easiest thing for me to do was bookmark what I found. To get started with creating a bookmark, I go to the view ribbon and select the bookmarks panel. Once that is open, I can click the add button to save my view.

With my bookmark created, I can now quickly click on it to restore my view. Mess with any of the settings and then click on the bookmark on the right to restore the saved view.

Updating a Bookmark

Sometimes you want to make an adjustment to a bookmark. Maybe you made a mistake or need to adjust your filters. Instead of deleting or creating a new bookmark, you can simply update it.

I can click the three dots to the right of the bookmark name to pull up a menu. There are a lot of settings in here, but we just need to focus on the update option for now. Just simply select update and your bookmark will be adjusted.

We also can take the time to rename our bookmark in the same place. Just select rename and choose one that makes sense. Use whatever works for you – it has to be better than “Bookmark 1”!

Build a Panel of References

One of my favorite things to use bookmarks for is to build out a list of views. When I am trying to create a seamless presentation, I build out my views and just start clicking through them. Before bookmarks, I could take screenshots and put them in a PowerPoint deck, but I lost the interactivity of the report!

To prepare for my presentation, I build out all of my bookmarks. Once they are created, I can drag and drop them into the right order. When I am ready to present, I open the report and show the bookmark panel. I can step through my presentation and provide quality reporting that contains an interactive component.

Extending Bookmarks for Flexibility

Another common use for bookmarks is to provide a little flexibility with your report canvas. They say you cannot make everyone happy. However, sometimes you can using a few bookmarks.

A common scenario I run into is picky report consumers who have specific report visuals they prefer. Some users want to see charts while others want to see tables. You could create a new report page with a different visualization, but that is a lot to maintain for a single visual. Keep it clean with a few bookmarks.

To get started, I will create my two visuals on the report canvas. It will look cluttered for now, but we will fix that shortly.

Adding a Matrix or Table Visual to the Report Canvas

The next thing I am going to do is open both the bookmarks and selection panels. I am going to use the selection panel to hide the table of values from the report canvas. Once it is hidden, I will create a bookmark and label it as “Chart”.

Hiding the Matrix Visual and Creating the Chart Bookmark

Then I will do the same thing to show the matrix visual instead of the chart.

Hiding the Chart Visual and Creating the Matrix Bookmark

Now I have these bookmarks available in the panel on the right and can use it as desired. Regardless of your consumer’s preference, they can pick the right visual for their needs. A simple and seamless solution!

Making Bookmarks Accessible

Now I already know what you are thinking – this is a great solution, but my users won’t know to use the bookmarks panel! I totally agree with you! Until recently, the only option you had was to add buttons and wire up your bookmarks. But with a recent update to Power BI, we can use the Bookmark Navigator to simplify that process.

Before we add the navigator, a best practice is to group our two bookmarks together. This will allow us to flip between our visuals without interfering with future bookmarks. To group our bookmarks, use control click to select our bookmarks, right click on them, and select group. We can then rename the group to something that makes more sense. I chose “Revenue Visuals”.

With that in place, we can go to insert and click on the button drop down. I can go down to Navigator and select Bookmark Navigator. It will then place the navigator on the canvas and add my bookmarks.

Adding the Bookmark Navigator to my Report

We are almost finished. If you remember what I mentioned earlier about grouping your bookmarks, you need to assign the group to the navigator in the format panel.

Set your Bookmark Group for the Navigator

The best part about this solution is that as you add more bookmarks to the group, you can easily add them to the visual. Just create a bookmark and add it to the group!

It is true – you cannot make everyone happy. But this might make a few extra people happy with a minimal amount of effort!

Anything Else About Bookmarks?

For report pages that do not have a lot going on, you might be fine with this setup. However, if you try to add a lot of visuals, you might run into performance issues. Instead of updating all visuals, consider changing the bookmark to update the selected visuals instead. Just use your control click on the selection pane to pick the visuals you want to show or hide and update the bookmark accordingly.

I will say, you will probably get your bookmarks wrong the first time. Even as experienced as I am, I occasionally need to keep updating my bookmarks until they are right. You might be frustrated, but don’t give up! I promise you it is worth it in the end!

Have you used bookmarks before? If so, how did you use them? Any favorite use cases? If so, tell me in the comments below!