Data with Dom

Sample Data Sets You Can Use for Trying New Ideas

On April 21, 2022

In General, Incremental Refresh, Power Query, Web Connector

We all want to hone our skills, but sometimes struggle to find good sample data sets to try out new ideas. Sometimes it is about specific data structure or maybe you want to show off an idea but cannot use production data. A lot of what I share comes from real scenarios I have encountered. To share these tips, I have had to use different data sources over the years.

This week, I want to share some data sets that I find fun and helpful for trying ideas out in Power BI. Some will be easier to use than others. You might even need to have a SQL Server to make them work. Regardless, you should be able to find something you can use.

The Most Basic of All Sample Data Sets

If you are brand new to Power BI, the Contoso Financial Sample workbook is a great place to start. It is a free and easy to use data set for beginners. While it does not a great resource for data modeling, it does serve as a quick and easy model to learn the basics of Power BI.

When I was a Power BI trainer, I liked using this data set for basic DAX calculations as some of the key measures such as cost of goods sold (COGS) where included in the model. I can perform some basic calculations which result in net profit.

Check out the Contoso Financial Sample data set here.

Learn How to Find Insights

Another one of my favorite sample data sets that is easy to use is the Pima Indians Diabetes Database from Kaggle.com. Like some of you, I cringe typing out the name of this data set. If published more recently, it would have likely been given a more culturally sensitive name. However, I use this data set for demonstrating the key influencers visual. I have also used it for predictive modeling with Azure Machine Learning, but that is for another day.

This data set was assembled by the National Institute of Diabetes and Digestive and Kidney Diseases. The purpose of the data set was to perform predictive modeling on diabetes in the Pima community. Identified as a high risk population for Type 2 Diabetes, this data represents the Pima community through years of research. Kaggle provides this data set for free. You just need to sign up for an account to access it.

Kaggle is a great resource for other data sets. There are so many to choose from, it is hard to just pick one. However, you are welcome to peruse their catalogue as you might find something interesting. With a little searching, you will find a data set which you can use to build a report on Settlers of Catan!

Check out the Pima Indians Diabetes data set here.

Simplest of SQL Sample Data Sets

Adventure Works is likely the world’s best know SQL database.. A common data set used for training, it is easy to implement. Experience with SQL Server Management Studio will serve you well as you implement this data set. Microsoft provides clear instructions on restoring the database but I find a little extra know how helps. It is wise to make friends with a database administrator if you don’t have one. Offer to buy them a drink or two at happy hour for their help and you will probably make a new friend out of the experience.

Download the Adventure Works data set here.

Binge The Office While Building Reports

Fans of The Office rejoice! TDMitch created a Dunder Mifflin sales data set from the Northwind Traders data base by Microsoft. Just like Adventure Works, this is a SQL data set. Implementing this data set requires additional effort compared to the Adventure Works database. You must follow instructions and run a few SQL scripts to finalize the setup of this data set.

I recommend this data set for someone who is trying to make something that connects with end users. I also recommend this data set for people who are expanding their transact SQL knowledge.

Check out the Dunder Mifflin data set here.

Simplest of REST API Sample Data Sets

REST APIs are great resources for 3rd party data. They work well but you might find frustration with implementing them. I have used this data set before with my series on the Basics of REST APIs in Power BI. While each API endpoint is unique, you can capture the basics using the Yahoo Finance API.

Offered for free up to 100 calls per day, it is an effortless way to learn the basics with no costs. If you are really into stocks, you might even consider purchasing a paid subscription. Spend some time digging through the endpoints and become comfortable with how you can use APIs with Power BI.

You can review the Yahoo Finance API documentation here.

Big Data Sample Data Sets

Sometimes you want to throw a lot of data to test out a solution. The New York City Taxi data set is a massive trove of data that is free for use. Available as CSVs or APIs, you can choose how you want to access the data. I used it to benchmark refresh speeds between various Azure data sources such as blob, table, data lake, and Azure SQL storage solutions.

The Taxi and Limousine Commission provides quality documentation around the data set. It even provides clear descriptions in the data dictionary, maps of taxi zones, and dimension tables. It even explains the difference between yellow taxi, green taxi, and for hire car services.

Check out the NYC Taxi and Limousine Commission data mart here.

Did Not Find Something To Fit Your Needs?

No fear about that! There are tons of free data sources out there for you to use. My favorite place to go is to data.gov and check out different data sets available from the US Federal Government. You can also search for open data from many states and cities. You might even be able to use it for some of your solutions.

Google also has a data set search that will help you find some samples. Search for different topics such as commodities or labor statistics and see what comes back. My only caution is that not every result will be free. However, if you are looking for something specific, this search will help you find what you data you need.

How about you? What are some of your favorite sample data sets? If you have a good one or used one of these, tell me in the comments below!

Conditional Formatting with a Column Chart?!

By Dominick Raimato

On April 14, 2022

In DAX, Report Design, Visualization Standards

In my article last week, I explored how to compare a goal built off last year’s revenue compared to this year’s actuals. Some of you might be thinking that system worked, but it felt cluttered. I would agree with you. I like using cluster charts in the right scenario, but I think we can do better. A couple of weeks ago, I did an article on why I love using conditional formatting to highlight important values. You also might remember that it was focused on using the table or matrix visual.

A little known fact is that you can leverage conditional formatting in other visuals. Imagine using conditional formatting to quickly identify which months we hit or missed our goal. I thought it would be great to dig into some options to really make your column charts pop!

Using Some Conditional Formatting Magic

A few weeks ago, I wrote an article on how to do perform field based conditional formatting. The same principle applies to building out our formatting in our charts. I start by building a measure that compares current year revenue to our goal.

CY Revenue Color = 
    SWITCH(TRUE(),
        [CY Revenue] >= [CY Goal], "Green", 
        [CY Revenue] >= ([CY Goal] * .90), "Yellow",
        "Red")

The formatting will show green if revenue exceeds our goal. We then identify any months that reached 90% of their goal in yellow to highlight that they were close, but did not quite make it. Then any months where we missed our goal, it now shows in red.

Now we need to apply the formatting to our chart. After selecting the visual, we need to go to the visual properties pane. Then you need to expand the columns section and click on the “fx” button. From there, just select the field value formatting style and select the CY Revenue Color measure we just created.

Applying our measure for conditional formatting.

The result is a chart that looks like this:

Field based conditional formatting on a column chart

It was a few extra steps, but it makes it clear we started the year off strong, but have some work to do to get back on track. Amazing what a little color can do!

This Is Great, But…..

I know what you are about to say – “I love the color, but it is hard to see how this compares to the actual goal. Can we do something about it?” Of course we can!

When traditional conditional formatting lets off, we can build our own with a series of measures. Using a stacked column chart and a few measures, we can show our revenue versus the goal to make it easier to see. We need to build four measures to make it easy to track against our goals: Revenue up to our goal, revenue that exceeds our goal, revenue short of our goal, and revenue left to meet our goal. Yes, it seems complicated, but just wait until you see the final product.

Our first measure will calculate the CY Revenue that is up to our designated goal. If we exceed the goal, we will just return the CY Goal instead and represent the remainder of the revenue in another measure.

CY Revenue to Goal = 
IF([CY Revenue] >= [CY Goal], [CY Goal], [CY Revenue])

Next, we want to show anything that is over our goal in another measure. Using a similar formula, we will calculate the difference of CY Revenue and the CY Goal. Note there is no else statement as we are only interested in revenue over our goal.

CY Revenue Over Goal = 
IF([CY Revenue] >= [CY Goal], [CY Revenue] - [CY Goal])

Next, we want to calculate the amount we missed our goal by taking by looking at months were we missed our revenue goal.

CY Revenue Short of Goal = 
IF([CY Revenue] < [CY Goal] && 
     MAX(SalesAggregated[Month]) <= EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

We don’t want our chart to show missed revenue for months that have not happened yet. We also don’t want to show missed revenue for our current month but rather as revenue to go. By using EOMONTH(), I am able to take the end of the month that we are in today and subtract a month so it will only summarize data before my current month.

Now doing something similar, I can calculate the revenue that still has to happen before the end of my current month. We will essentially do the same measure as above but looking forward.

CY Revenue to Go = 
IF(MAX(SalesAggregated[Month]) > EOMONTH(TODAY(), -1), 
     [CY Goal] - [CY Revenue])

And with a little color assigned in the, we can make our chart pop!

Homemade conditional formatting using some measures and custom colors

Which Method When?

Both methods work really well for providing conditional formatting. But one obviously takes a lot more effort to create. And beyond that, I need to make sure my DAX is clean and doesn’t slow down my report.

Why would I go down this route? I find that sometimes you have a stakeholder who really likes to get into the minutiae of the data and wants to be able to accurately see their plan versus actuals. This might have been a little more complicated than required, but I wanted to show you how granular you can get.

From my experience, most people do not want to get this granular with their reports. But in case you run into someone who really wants some additional detail, you now know how to make it happen.

Have you run into something like this before? Have you been asked to add some formatting to your column charts? Are you going to start doing it? Tell me in the comments below!

What If Scenarios in a Power BI Data Model

By Dominick Raimato

On April 7, 2022

In DAX, Report Design, Simulation

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

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

Setting Up Our Revenue Measures

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

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

CY Revenue = 
SUM(SalesAggregated[Revenue])

Which results in this column chart:

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

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

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

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

How Does a What If Parameter Work?

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

Creating a What If Parameter

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

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

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

Setting Up a What If Parameter in Power BI

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

What Else Is Created With a What If Parameter?

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

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

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

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

Is There Any Other Setup Required Before We Use It?

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

Formatting What If Parameters for Percentages

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

Our What If Parameter on the Report Canvas

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

Applying a What If Parameter

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

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

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

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

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

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

Next Steps

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

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

Mr. Bad Example – Bar and Column Charts Best Practices

By Dominick Raimato

On March 31, 2022

In Best Practices, Visualization Standards

I cannot begin to express how often I see bad examples of data visualization. Sometimes it is unintentional but it can have far reaching implications. Not following best practices and visualization standards often misleads report consumers and eventually bad decisions.

Even worse, report creators often manipulate these visuals to get the data to support their narrative. I am all for sharing a narrative with data but I draw the line at manipulation. If you have to twist and manipulate your visuals to tell your story, it is probably time to get a new story.

I am passionate about this topic because I watch people manipulated by these visuals all of the time. Sales presentations, all hands meetings at work, poll results, and even the news media commit these violations. My goal for this article is two fold. The first is to help you understand the why of these best practices for your own reports. The second is to help you identify these violations in the wild and help call them out.

My articles usually focus on Power BI. However, this article applies to any visualization tool you will encounter.

The Importance of Best Practices and Visualization Standards

You might be asking to your self “why is Dominick so passionate about this?” To me, the loss is considerable if you are not careful. Here are some common scenarios where not following best practices can get you into trouble.

Financial Costs

This is the first scenario that often comes to mind when it comes to bad visualizations. We use reports and visualizations to make data driven decisions.

Let’s pretend you own a store that sells widgets. You take a look at a chart that shows your sales doubling month over month. As a shop owner, you might double your order for next month because if the trend continues you want to be prepared. It totally makes sense! You double up your orders (or even triple them) to meet demand.

Column Chart Showing Revenue Doubling Each Month

However, I left something out of this chart. There was no scale and my axis did not start at zero. In fact, I never actually doubled my sales. I only had a 10% increase of sales month over month.

Column Chart Showing the Same Data Above with Proper Scale and Labels

Now you might be thinking that this really is not a big deal as widgets do not have an expiration date. But the implications of this could be considerable as you took cash in hand and put it into inventory. You still have an assets on hand with the widgets, but you will need to sell them to make sure you have cash on hand. This could put you in a bind as an owner as you could potentially become “cash poor” which results in trouble paying bills, making payroll, and even paying yourself.

Someone Could Get Fired

Have you ever created a report that could get someone fired? I have!

One of the most common reports I see are around service level agreements – also known as SLAs. SLAs determine effectiveness of a process or service. Contracts include these metrics that help determine success of services.

Remember when pizza shops had the “30 minutes or its free” guarantee? It went away before I was old enough to buy my own pizza. Likely because someone did an analysis of their metrics and found they were not delivering enough pizzas on time.

If you managed a pizza shop, you might have had an SLA of delivering pizzas in under 30 minutes 97% of the time. That means out of 100 orders, three of them could be free because they did not make it on time. Your district or regional manager likely has a report showing your performance. What if they showed a report that showed a trend where your on time delivery metric dropped significantly. You might be in danger of losing your job for not performing as expected.

But if the chart is only moving from 99% of the time to 98%, it is still above the SLA and overall not too bad. The point here is that visualizations not following best practices can imply someone is not performing well in their job. This leads to someone getting disciplined even if they are performing well in their job.

Your Future Ambitions

People are often looking for ways to grow their career. They put together statistics and figures to show how great they are and why they deserve a raise or promotion. They might also provide some of the same information to help those above them move up the ladder as well. But what do you think the implications are if your visualizations used for this purpose are found to not use best practices?

I see it as a breech of trust. While it may seem insignificant at the moment, the impact could be long term in your career. Every report created could be scrutinized and called into question. It seems crazy but the potential is there. If a decision made from the report costs your organization a considerable amount, you might even lose your job!

This is why I believe it is important to consider best practices whenever creating reports. Leaders must take the time to make sure their teams understand these practices and live them for the benefit of their team.

What Should I Know About Best Practices?

There are 4 best practices that you should be concerned with when you create your reports. Follow these and you will be well on your way to building quality reports.

Best Practices to Follow #1 – Always Start at Zero

This is a non-negotiable standard when it comes to bar and column charts. They must always start at zero to avoid misleading consumers. This is the most common infraction that I see when it comes to data visualizations.

As discussed above, I can show amplified trends by changing the axis to start at a higher value. This also causes confusion and frustration for report consumers.

Best Practices to Follow #2 – Label Your Axis

The next most common ignored best practice is labeling your axis. We see the categorical axis getting a label (category, time, etc.) but he value axis does not get the same love.

To avoid confusion, you will want to label your value axis to match the measures in your chart. There is some flexibility with this as you can change the units to make it easier to read. For example, you can set the units to millions to compress the chart and make it easier to read. While you might be distorting the view some, the labels will clearly define the scale and make it easier to understand.

Labeling Your Categorical Axis and Value Axis

I also recommend using some lines to highlight your axis across the chart. I worked with an organization a few years ago that hated those lines. It was forbidden to add them to any chart in a presentation. While I strongly disagreed with this approach, they used data labels which helped keep things in context when viewing a chart.

Best Practices to Follow #3 – Use Data Labels to Highlight Key Values

My friend and colleague Sally Paczosa always used the term “hover to discover”. I stole her line and say it all the time when it comes to reviewing reports in Power BI. If I want to know the actual number, I can hover over a column or bar and see what the value is. It is one of the best features that people forget about when it comes to reporting.

But as much as I love it, I always have to consider my audience for a report. If my consumers are fluent with tools like Power BI, they already know what to do. Without proper training, they might miss that opportunity and never actually do it. In the same vein, I have to consider the venue they will consume the report. They can “hover to discover” all day long at their computer. But what if they are in a town hall meeting looking at a chart being presented? How can they view the actuals?

Data labels allow us to quickly identify key measures on a report and allow us to add context quickly. This prevents the fatigue of trying to match columns to the axis and accelerates understanding.

Best Practices to Follow #4 – Add Lines for Clarity

I like adding lines to help provide better context and clarity to a chart. This is extremely helpful for reports that will be consumed without a talk track behind them. You can highlight common statistical measures such as maximum, minimum, mean, or median. You can even add a constant line if you want to highlight a particular goal. If not used properly, it could clutter your charts. However, properly placed it could make a huge difference.

Adding a Constant Line to Highlight a Goal

Best Practices Apply to Chart Types as Well!

Which is better? A bar chart or column chart? The answer will depend on the context of your data and the intent of your visualization. Here are some thoughts to help you make the right selection

Column Chart vs Bar Chart

People tend to use these terms interchangeably but don’t realize there is a difference between a bar and column chart. In a bar chart, the data is represented horizontally. Column charts represent the data vertically.

While it may seem inconsequential to interchangeably use these terms, it is important to really understand the difference to help represent your data.

Column Charts

Column charts can be used for categorical data, but they often shine when it comes to series based data. The natural left to right progression of the canvas allows users to follow a timeline to see trends.

They also shine because the vertical orientation of the values is easier to grasp some statistical measures such as maximum and minimum. It is less cluttered in this orientation and the labels are easier to read.

Bar Charts

Bar charts do an amazing job of showing large amounts of data in a compressed area. If you have long list of category labels, they tend to fit better horizontally. This prevents crowding and confusion.

In addition, they do a great job of allowing users to scroll down for large amounts of data. Naturally, your venue is going to have an impact on this functionality. You cannot just scroll down on a chart if you are in the audience of a town hall meeting. But for reports that are consumable at your desk, it is easy to scroll down and see more information.

Stacked and 100% and Clustered, Oh My!

Now that you have a hint at which direction you want your charts, it is time to pick the right one. If you only have a single series of data, then you could pick a stacked or clustered chart and it would not make a difference. But depending on what you are trying to show, it is important to pick the right chart.

Stacked

Stacked charts show the composite makeup of the whole value. Let’s stay I own a store but recently started selling my products online. I might want to see the breakdown of my revenue from my store versus online. A stacked column chart allows me to quickly identify the makeup of revenue which can help provide context. For example, if I have a month with higher than expected revenue, I might see that online sales were higher than in the past.

While this chart style is versatile and useful, it is not always easy to make comparisons. The orientation of the values makes it difficult for one to one comparisons.

100% Stacked

Similar to the stacked chart, 100% stacked charts show data in a similar format. The biggest difference is that the bar or column do not show totals. Instead, it shows the percentage of the composite elements.

Take our store versus online sales question. If you looked at the chart above, you could see the breakdown. But what if I wanted to understand the percentage of my sales that were in my store versus online. I could do some math to figure it out, but it is easier to show in the 100% stacked chart.

This is where the 100% stacked chart shines. It allows us to understand distribution a little easier which gives us a better comparison. We can quickly identify trends of online sales and see if they are increasing, decreasing, or remaining flat over time. The only downside is that you cannot see the overall totals.

Clustered

Clustered charts allow for quick comparisons. Not as precise as the other two charts, it does a good job of allowing you to compare two values side by side. Where most people go wrong is they try to compare categorical data which can get confusing. I like to use it to compare year over year or even month over month data. This allows for a close comparison without a lot of clutter.

What If I Am Unsure of Which One to Pick?!

The best part of most tools is that you can quickly change which chart you are using! Try some different ones. Maybe you will need to use a couple of variations to get your data to appear correctly. The goal is to communicate a story. One of my favorite ideas is to show the visualization to someone and have them interpret it for me. Don’t give them any hints – let them tell you. If what they say matches your narrative, then you are likely on the right track!

Next Steps

Now that you have a better understanding of best practices when it comes to bar and column charts, what do you do next? My first recommendation is to go back through your reports and presentations and find out if you were following these best practices. If you weren’t, try to understand why. I find that we tend to take shortcuts to save time when we have a hard deadline coming up. Building in a little extra time in your plan helps ensure nothing gets left out.

What about when we see best practices not being followed? What do we do? I find most people make these mistakes out of ignorance. They might know all of the best practices or how to apply them. I always recommend coaching people and helping them learn. I also find being the best example helps change the behaviors around you. By getting others to follow your lead, you will find others will do the same.

How often do you see these best practices ignored? Do you have any egregious examples? If so, tell me in the comments below!

Conditional Formatting – Moving to the Next Level

By Dominick Raimato

On March 24, 2022

In Report Design, Visualization Standards

Last week, I went through the basics of conditional formatting. I shared how it helps take your reports to the next level and the steps required to use it. We looked at gradient and rule based conditional formatting but did not touch the field option. I wanted to split this one out because it can be a little more complicated but is quite versatile.

What is Field Based Conditional Formatting?

Isn’t all conditional formatting based upon fields? Of course it is! We are always looking at field values to activate conditional formatting. Field based conditional formatting is not looking at the field values for what needs to happen. Instead, it is looking to the field for what formatting is supposed to appear.

Let’s use our example from last week where we looked at revenue versus goals. We used a simple set of rules to highlight table rows as red, yellow, or green based on values.

Enabling Rule Based Conditional Formatting

Instead of setting a bunch of rules, we can create a measure that specifies these colors. If you remember a few weeks ago, I did a post on using the SWITCH() function in DAX. I am going to use it to create a measure to specify the colors I want to use.

Field Formatting v1 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "Red",
        SumRevenue < 1500000, "Yellow",
        SumRevenue >= 1500000, "Green",
        "Gray")

Note that I am using colors by name in my measure. It is not fancy, but it gets the job done. Once the measure is ready, we just need to apply it to the column. I select field as my format type and then select the measure I just created.

Setting Up Field Based Conditional Formatting

The result is my table formatted as expected.

Results from Field Based Conditional Formatting

And just like that we are using a measure to drive our conditional formatting.

This Looks Great! Can I Customize My Colors?

You can absolutely customize your colors! Instead of writing out the words, you can use hex codes to set the color.

Field Formatting v2 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a00",
        SumRevenue < 1500000, "#f7da00",
        SumRevenue >= 1500000, "#006616",
        "Gray")

Using a branding guide to help me, I pulled the red, yellow, and green hex codes to use in my report.

Field Based Conditional Formatting Using Hex Color Codes

But now my colors are a little too dark How do I fix that? Here’s a little pro tip – you can add two numbers on the end of your hex code to determine the transparency of the color. I wanted to soften the colors a bit and make it 50% transparent, so I added a 50 to the end of each hex code.

Field Formatting v3 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(TRUE(),
        SumRevenue < 1450000, "#9c0a0050",
        SumRevenue < 1500000, "#f7da0050",
        SumRevenue >= 1500000, "#00661650",
        "Gray")

And the difference is dramatic!

This makes a huge difference in our formatting and it is much easier to read.

Why Go Through All Of This Effort For Conditional Formatting?

If you are anything like me, you are probably wondering why you should go through this kind of effort to build out measures for conditional formatting. Every tool has a purpose and this is one that rarely gets leveraged. But consider some of these use cases where you might find it useful.

Complex Rules

When using rules, you find that you are put into a simple set of conditions that drive your formatting. Maybe you have a complex set of conditions that you need to consider. Instead of a simple greater than, less than, or between set of rules, you can build out some complex logic.

For example, you only want the colors to appear for the months in Q4. You cannot accomplish this with the standard set of rules. But with a little extra DAX, you can make that happen.

Field Formatting v4 = 
VAR SumRevenue = SUM(Revenue[Revenue])
RETURN
    SWITCH(QUARTER(MAX(Revenue[Month])) = 4,
    TRUE(), 
        SWITCH(TRUE(),
            SumRevenue < 1450000, "#9c0a0050",
            SumRevenue < 1500000, "#f7da0050",
            SumRevenue >= 1500000, "#00661650",
            "Gray")
    )

If you wanted to get really crazy, you can build out a complex measure that darkens the color the closer you get to the due date of a goal. The sky is the limit!

Color Flexibility

What if you had a report that can be filtered for multiple customers? You could setup your formatting to highlight values using their primary color color scheme.

If you have a customer table, you can specify some key palette colors and reference them in your SWITCH() statement. This enables a personal touch for each customer without having to create separate report pages or files for each one. Talk about scaling out your reporting!

Anything Else I Need To Know?

Remember, you are building out your conditional formatting using measures. You always want to optimize them to be efficient and not tax your report canvas. You are adding additional calculations to your report canvas, so don’t overdue it!

So how about you? Have you used field based conditional formatting before? If not, do you have a use case? If so, tell me about it in the comments below!

Draw Attention with Conditional Formatting

By Dominick Raimato

On March 17, 2022

In Report Design, Visualization Standards

Conditional Formatting is a powerful element that enhances your reporting. When asked for feedback on a report, I often recommend users use some form of conditional formatting to help draw attention to key elements. I won’t lie to you – it takes extra effort to make it work. However, with a little planning you can make your reports pop while guiding your readers to the most critical information on the canvas.

Why is Conditional Formatting Important?

Conditional formatting takes good reports and makes them great. When you design a report canvas, it can quickly become cluttered with too much information. It overwhelms the viewer and causes anxiety. As a result, energy is wasted on trying to understand what the report says. Conditional formatting helps resolve this issue for the viewer.

Conditional Formatting In Your Car

Think about driving a car and looking at the dashboard. There are several measures and metrics to tell you what is happening. A speedometer tells you how fast you are traveling. A tachometer tells you how many revolutions your engine is making in a minute.

However, some of the gauges have a little help. For example, your fuel gauge tells you how much fuel is in your tank. It is a simple – when the gauge reaches “E” or “0”, it is time to get more fuel. But with the bustle of everyday life, sometimes we forget to check the gauge. That is when the low fuel light comes on and alerts us that we need to stop and fill up. The conditions are simple – when the fuel level reaches a low enough point, the light turns on.

Now, the light is not really necessary. It is not required for your car dashboard to work. Yet, we find it useful as it draws our eyes to a potential concern so we can resolve it. It alerts you to an issue that pairs with an action to resolve it.

The check engine light works in a similar fashion. It alerts you of a problem that you have to address. Unlike the low fuel light, the check engine just tells you there is a problem. You will have to do a little more research to find the issue. If you don’t have access to a scanner, you might need to take your car to a garage to have it checked out. Either way, you know something is wrong and can resolve it before it becomes a bigger problem.

Augmenting Reports with Conditional Formatting

Conditional formatting does not replace your current report visuals. It draws the eyes to key information on the canvas so you can act. With a little splash of color, you can draw eyes quickly to important data points.

Now, you might be asking “don’t I need a legend or something to tell people what they need to know?” Believe it or not, you really don’t need to do much. If you highlight a value in bright red, almost all viewers will understand that it is bad. When you highlight something in green, it is good. That does not take much effort to understand.

The trick is properly apply the conditional formatting so users are able to understand what is on the canvas.

Applying Conditional Formatting

So all of this sound great. But how do I apply it?

I find the easiest place to apply it is with a table or matrix visual. Even though Power BI has lots of rich visuals, many people still hold on to their PivotTables from Excel. They love seeing data in a tabular format. This is a great introduction to how to apply conditional formatting in a standard report.

For this article, we will be looking at a simple table that lists sales revenue by month for 2021. Our goal is to call out values that need attention so our consumers can understand them.

Highs and Lows with Gradient Formatting

The table above lists out how much revenue was received per month. What if I asked you which month had the highest revenue. You are pretty smart, so you could figure it out fairly quickly. But what if the table had 5 years of revenue? It would take a considerable amount of brain power to figure it out. You could do it, but it would not be easy. Gradient formatting aides viewers in seeing which months had the highest revenue.

To start, we have to enable conditional formatting on the cell backgrounds. We will simply select the table visual, go to the visual properties, expand cell elements, select the revenue column, and turn on the background color element.

Enabling Conditional Formatting on a Table

You will see that we can easily identify July 2021 as the month with the highest revenue. We also can see February as the month with the lowest revenue. There is no denying it – conditional formatting makes it much easier to read!

But this just shows highs and lows. What if I have a revenue goal that I want to meet. Gradient conditional formatting does not meet that requirement. So how do we take our visualization to the next level?

Thresholds and Goals with Rule-Based Formatting

Instead of highlighting on a gradient scale, we can set formatting on a series of rules. To get started, select the function (fx) button under background color. This will pop open a new window to allow us to make our changes.

From here, we will change the Format Style from Gradient to Rules. After that, I needed to add two more rows of rules to achieve my goal. From there I can establish my rules to highlight which months did not reach my revenue goal of $1.5 million dollars.

The first rule is to show anything that missed my revenue goal. So I will start with zero to $1.45 million. I then assigned the color red to that field. Why not $1.5 million? While that is my goal, I want to highlight any revenue amounts that were close to my goal, but did not quite make it. So my next rule highlights anything between $1.45 and $1.5 million with yellow. And finally, anything over $1.5 meets the goal and is highlighted in green. However, I need a top end value for that rule, so I just put $5 million in there as a place holder.

Setting up Rule-Based Conditional Formatting

With my rules in place, it is much easier to tell which months met or missed the goal of $1.5 million in revenue.

Highlighting Values with Rule-Based Conditional Formatting on a Table

It is easy to see that we hit our revenue goals in eight months of 2021. We missed our goals in February, April, and May. And while we were close, we did not quite reach our goal in June. This formatting draws our eyes and makes it easy to where we missed the mark so we can adjust our plans for 2022.

Can I Format the Entire Line?

The short answer is yes – you can do that. The only catch is that you have to update the rules for each individual column in your table. Just select the Month column and start making your changes.

The biggest thing you need to do is change the field the formatting is based upon. It defaults to the column you are formatting. However, we need to set it based upon the values in the revenue column.

Formatting Conditional Formatting Rules for the Month Column using Revenue

Set the rules to match your original formatting and you will have the entire line formatted.

Conditional Formatting Applied to All Columns in a Table

With all of this in place, I have provided conditional formatting leveraging the background color. This was easy because I only have two columns in this table. If I had a lot of columns, it might be more time consuming to setup.

Now, as nice as background colors are, I am constantly asked if there any other options out there. The answer is of course!

What Else Can I Do with Conditional Formatting?

I like using a cell’s background color but there are other options out there. If you want to use color, but the cell background is too bold, then you can use font color instead.

Conditional Formatting Using Font Colors

One of my personal favorites is to use icons with the report.

With icons, you can select different styles that suit your needs. You also have the ability to move the icon to the left or right of the values in the table. And if the occasion calls for it, you can just use an icon in the field instead of a value.

Setting Up Conditional Formatting Using Icons

The other favorite is to leverage data bars. This aligns better with gradient conditional formatting, but helps show magnitude of the values better.

Regardless of what style of conditional formatting you use, I promise you it will elevate your reports and make them easier to understand.

Next Steps with Conditional Formatting

So with this knowledge of conditional formatting, should I know anything else? Of course – the biggest thing is knowing when to use conditional formatting. Quality report design rules still apply. Overuse of conditional formatting can cause your report canvas to look cluttered and confusing. It also could impact performance as calculations are taking place in the background to enable the formatting. Just remember that less is more.

The other thing you know is that conditional formatting can be applied to more than cells in a table or matrix. In fact, there are other elements of conditional formatting to share. I have an upcoming article that will dig into those features, so keep an eye open for it!

Until then, have you used conditional formatting? Do you have a use case ready to go? If so, tell me in the comments below!

Using the POST Method with REST APIs Inside Power BI

By Dominick Raimato

On March 10, 2022

In Power Query, Web Connector

Earlier this week I spoke about the Basics of Connecting to REST APIs at the Global Power BI Summit. A lot of people found it valuable, but wanted to know more about how to use the POST method to get data. Specifically, they wanted to know how to add a payload with the request to make it work. Since there was a lot of interest, I decided to write an article on this topic and share it with everyone.

For this article, I will be using the Microsoft OAuth2 endpoint to get an authorization token. You can find detailed documentation on the endpoint here. I will not be going into the details of the process to create an app registration or apply it in the Microsoft environment as that will take some time to cover. Instead, I decided to focus on the most important part of the request – the POST Method!

No/Low Code No More!

For many, this will be the scariest part of the entire process. This might be your first time ever writing code for a query from scratch. We do not have a connector to help us with this process. Fear not as we will walk through this entire process together and break it down. So to get everything started, go to Get Data and select Blank Query.

Once you select Blank Query, you will be redirected into Power Query with a blank canvas. I like to quickly update the query name on the right hand side before I forget. I am calling mine “Get-AuthToken”. Once that is in place, we will open up the Advanced Editor in the home ribbon. That is where the real fun will begin…

POST Method Query

Now that the Advanced Editor open, we are going to start writing out the query. We will need to write out a web connector query and a message body to make this work. I like to start with the web connector and build out from there.

Building the Initial Connection Query

Currently, we have Source = “” in our editor window. Those quotes are going to be the base of our query. We are going to start by adding the endpoint url in between the quotes. We should see something that looks like this:

let
    Source = "https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token"
in
    Source

In order to specify how we are connecting to the endpoint, we will wrap it with Web.Contents:

let
    Source = Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token")
in
    Source

After that is in place, we need to add our headers. These are specified in the documentation and will be unique to your API. This example uses two headers and they are required to achieve the desired result. Note the comma between the url and the headers.

let
    Source = Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])
in
    Source

Lastly, because the header is specifying the response in JSON, we will wrap the entire string with Json.Document to translate the response.

let
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

With that code in place we have our base query built. If you want to save some typing, you could always use an existing query as a template. Just copy and paste it into the blank query and make your changes.

Now that we have the base query written out it is time to focus on the message body.

Body Building without Protein

The body comes by a few different names. You might see it as message body, query, payload, or simply “The Data”. Essentially, it is additional information that is passed so the endpoint works as expected. While you don’t have to lift weights for this step, you might find it just as frustrating. I have tried this a few different ways, but found this to be the easiest method.

The first thing we will do is create a line right above our Source line and below the let line. From here, we will enter ContentBody = “”, as a place holder. Make sure you add the comma at the end of the line!

let
    ContentBody = "",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

My example requires five different fields be specified in the body. The formula is simple: field name = field data. We will add an ampersand at the end of the line and return to the next line to make it easier to read.

let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

As a best practice, I keep elements that might need to be changed at the bottom for easier access. This example leverages a client secret that will need to be updated over time. To make it easier to access, I leave it at the bottom.

Add the Body to the Web Connector

To finalize your query using the POST Method, you have to add the message body to the source line. We need to pass the body as content. However, I need to convert it into binary. We are just going to add Content=Text.ToBinary(ContentBody) inside of the first set of brackets so it passes the body in the web connector.

let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]]))
in
    Source

Now that everything is in place, we can click Done and let the query do its magic!

Response from my POST Method query that brings back an access token for authentication.

To take things to the next level, I can drill down on the actual token by right clicking and selecting drill down. Next I will convert the query to a function by adding ()=> above the let line. In doing so, I will be able to leverage it for multiple endpoints that require token based authentication with a simple call.

()=>
let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
        client_id=<CLIENTID>&
        client_secret=<CLIENTSECRET>",
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/<TENANTID>/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])),
    access_token = Source[access_token]
in
    Source

Make Life Easier with Parameters

I plan to write a more detailed article on Parameters inside Power Query but I want to share a little tip that will make your life much easier. With this example, we are connecting to an app registration inside Azure Active Directory through a Client ID and Client Secret. The Client Secret will expire at some point and needs to be refreshed. To make your life easier, you can add your dynamic values as Parameters so you can update the query quickly.

Go to your home ribbon and click Manage Parameters. Click new and add your parameter name. Lastly, add your parameter value and click OK when finished.

Setting up parameters to simplify the process to update my client secret.

Now we just need to replace the values in our query. We will have to use the ampersands to concatenate our values together so they come together in a single string.

()=>
let
    ContentBody = 
        "Content-Type=application/x-www-form-urlencoded&
        scope=https://graph.microsoft.com/.default&
        grant_type=client_credentials&
    	client_id=" & ClientId & "&
        client_secret=" & ClientSecret ,
    Source = Json.Document(Web.Contents("https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/token", [Content=Text.ToBinary(ContentBody), Headers=[Accept="application/json", ContentType="application/x-www-form-urlencoded"]])),
    access_token = Source[access_token]
in
    access_token

Now if I need to update my client secret, I can just update the parameter instead of breaking open the Advanced Editor. I can even do it from the settings view in the Power BI Service. But that will have to wait for another article.

Final Thoughts on the POST Method

It is important to note that not all POST Method endpoints are meant for querying in Power BI. The primary purpose of the method is to create items. However, it is not unheard of for querying rows of data. Before you do anything with an endpoint that uses the POST Method make sure you review the documentation. You might accidentally write data to your endpoint if you are not careful!

Do you have a use case for querying using the POST Method? Any favorite endpoints? If so, leave a comment below!

SWITCH()-ing Up Your Conditional Statements

By Dominick Raimato

On March 3, 2022

In DAX, SWITCH

Excel Power Users are fans of the classic IF() statement. Powerful as it may be, it can get quite confusing when you nest them together. If you have never used the SWITCH() function in DAX, then you are missing out on an opportunity to simplify your conditional statements.

Complications of IF() Statements

Like you, I have used IF() for years. It has served me well and I have relied upon it for years. However, I always found it cumbersome when I needed complicated logic.

Let’s start with a basic list of vehicle models manufactured by Ford. If I wanted to quickly flag a particular model, I could do so with a basic IF() statement.

Ford F-150 Flag = 
IF ('Model List'[Model] = "F-150", "Yes", "No")

List of models produced by Ford Motor Company

Where it gets complicated is when you nest your IF() statements. If I wanted to categorize the vehicles by type (truck, SUV, car, etc.), this would require a more complicated statement.

Vehicle Type IF = 
IF ('Model List'[Model] = "F-150", "Truck",
IF ('Model List'[Model] = "F-250", "Truck",
IF ('Model List'[Model] = "F-350", "Truck",
IF ('Model List'[Model] = "Ranger", "Truck",
IF ('Model List'[Model] = "Maverick", "Truck",
IF ('Model List'[Model] = "Mustang", "Car",
IF ('Model List'[Model] = "Explorer", "SUV",
IF ('Model List'[Model] = "Escape", "SUV",
IF ('Model List'[Model] = "Expedition", "SUV",
IF ('Model List'[Model] = "Edge", "SUV",
IF ('Model List'[Model] = "Bronco", "SUV",
IF ('Model List'[Model] = "Transit", "Van", "Error" 
))))))))))))

While effective, it becomes complicated. The DAX is bulky and requires you to maintain a number of parentheses to complete the formula. While harmless on its own, it could become cumbersome if you need to interject this into a larger formula.

Now, you might be reading this code and say “Hey Dom! There is a way to simplify this IF() statement! ” You are correct and I will review those methods shortly but let’s keep the statement simple for now.

SWITCH() It Up and Clean It Up

I can take that basic IF() statement and simplify it using the SWITCH() function. This function allows you to simplify the logic so it is easier to manage. The basic syntax of the function is:

SWITCH(<expression>, <value>, <response> [, <value>, <response>, … ] [, <else>])

Simply put, this function will evaluate an expression and compare it to a list of values and provide the appropriate response. It also contains an else clause which fills in any other gaps you might have. If we apply it to our complicated IF() statement, we can simplify it to this:

Vehicle Type SWITCH =
SWITCH ('Model List'[Model],
    "F-150", "Truck",
    "F-250", "Truck",
    "F-350", "Truck",
    "Ranger", "Truck",
    "Maverick", "Truck",
    "Mustang", "Car",
    "Explorer", "SUV",
    "Escape", "SUV",
    "Expedition", "SUV",
    "Edge", "SUV",
    "Bronco", "SUV",
    "Transit", "Van",
    "Error"
)

Now that is much easier to read and understand. The SWITCH() function will evaluate which model is selected and then check it against a list of values. If the model is found in the list, then it will bring back a response. If the model is missing, then it will bring back “Error” as specified in the else statement.

But the really cool part about the SWITCH() function is that we can flip it upside down. Instead of specifying an expression and checking it against a value, we can specify a value and check it against a list of expressions. Let’s say we want to bucket the price of the F-150 line of trucks. Since there are different trim packages, the range is quite considerable. We can use SWITCH() to accomplish this goal.

Price Buckets = 
SWITCH (TRUE (),
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

In this expression, the value we are looking for is TRUE(). From there, our list of expressions will come back either TRUE() or FALSE(). I can still accomplish the same with an IF() statement, but this is so much cleaner.

Price Buckets =
IF ('Model List'[Price] <= 36000, "Low",
    IF ('Model List'[Price] <= 55000, "Mid",
        IF ('Model List'[Price] > 55000, "High"
        )
    )
)

Both methods work well, but the SWITCH() function keeps the code clean. This will be important as we start to build more complicated measures.

SWITCH()-ing Directions

So far we have only been working with simple logic statements. However, sometimes these logic statements become far more complicated. In our list of F-150s, there are two models which are classified as emergency vehicles. They are not to be sold to the general public. We also have a sport and electric trucks that are not part of our standard report. As a result, we want to clean up our reporting and remove them from our pricing buckets. Let’s try a few different ways to manage this.

Option 1 – Add Model Types to the SWITCH() Statement

The easiest option to start is to add the model types to the SWITCH() statement we used above. This will require us to add three values to our SWITCH statement to clean up the list.

Price Buckets v1 = 
SWITCH (TRUE (),
    'Model List'[Model] = "Emergency", "N/A",
    'Model List'[Model] = "Electric", "N/A",
    'Model List'[Model] = "Sport", "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

There is nothing wrong with this method as it works well, but we can simplify those three lines into a single line.

Option 2 – AND/OR Modifiers

In the first section I mentioned there was a way to simplify our IF() statements. Excel users are very familiar with using AND()/OR() statements to handle multiple logic arguments at once. The only catch is that these functions can handle two arguments at once. Since we have three vehicle types to consider, we will need to chain two items together.

Price Buckets v2 = 
SWITCH (TRUE (),
    OR ('Model List'[Model] = "Emergency" OR ('Model List'[Model] = "Electric", 'Model List'[Model] = "Sport")), "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

This method works, but like the IF() statements, they get complicated very quickly. This is not clean and hard to maintain if you want to modify the list of model types in the future.

Option 3 – Using AND/OR Without Functions

A hidden secret with conditional statements is how to use AND/OR without the function. We can substitute AND() with “&&” to chain as many statements together as we want. Also, we can substitute OR() with “||” and accomplish the same thing. This eliminates the need to manage the parentheses associated with the functions

Price Buckets v3 = 
SWITCH (TRUE (),
    'Model List'[Model] = "Emergency" || 'Model List'[Model] = "Electric" || 'Model List'[Model] = "Sport", "N/A",
    'Model List'[Price] <= 36000, "Low",
    'Model List'[Price] <= 55000, "Mid",
    'Model List'[Price] > 55000, "High"
)

This method still works well, but it is still not the cleanest way to keep everything together.

Option 4 – Combine SWITCH() Statements

Since we have a specific list of vehicle types that should not be bucketed, we can use one statement to handle which vehicle types should not be bucketed while another assigns buckets to vehicles that can be bucketed.

Price Buckets v4 = 
SWITCH (TRUE (),
    ( 'Model List'[Model] ) IN { "Emergency", "Electric", "Sport" }, "N/A",
    SWITCH (TRUE (),
        'Model List'[Price] <= 36000, "Low",
        'Model List'[Price] <= 55000, "Mid",
        'Model List'[Price] > 55000, "High"
    )
)

In this sample code, we have used two SWITCH() functions. The first function simply checks the vehicle model information. I even cleaned up the expression to evaluate the vehicle model similar to a IN expression found with SQL statements.

I then took a second function that came through in the else statement to evaluate the price bucket for models not already dispositioned in the first SWITCH() function.

Thinking About SWITCH()-ing?

Now, I will admit that I am complicating the formula in that last option. But I wanted to show how easy it can be to inject a SWITCH() function inside of an existing function. It took me a while to get used to using SWITCH(). In fact, before writing this article, I still used IF() because I did not think I could chain AND()/OR() functions. After trying it out for this article, I even learned that I can probably eliminate IF() from most of my DAX formulas.

Are you using SWITCH() at all with your DAX formulas? Do you think it is easier than IF()? Tell me in the comments below!

Mobile Reporting That You Can Actually Use!

By Dominick Raimato

On February 24, 2022

In Report Design, Visualization Standards

The February 2022 update of Power BI came with a major change for mobile reporting. It was so exciting that I dropped my original article for this week so I could share this update. Mobility has always been a part of Power BI dashboards, but there were some challenges. It was not flexible and did not offer much with visual interaction.

Mobile reporting was then added to Power BI reporting, but there was still one major challenge. What worked for consumers on a computer or tablet did not work for mobile phones. To make it functional, you might end up creating two different versions of the report to accommodate mobile users.

With the February 2022 update of Power BI, we can finally take an existing report and modify it for a quality mobile experience. This is a huge step forward for the frontline and I cannot wait to use it!

Data on the Frontline

Data gurus pontificate about how leaders should make data driven decisions. I agree with them. I love having the right data to make decisions. With some analysis, I have likely avoided making poor purchases because of my research.

As wonderful as many of these data gurus are with their ideas, there seems to be a prejudice towards frontline leaders. The gurus spend their time reviewing data about what happened and how they can turn their insight analytics to prescriptive analytics. But the frontline workers are hungry for data and welcome it for making decisions.

Moving and Shaking on the Frontline

Frontline leaders spend their day on their feet. They are normally walking the floor pitching in where needed. If they are in retail, sometimes they are running registers while associates are taking breaks. In manufacturing they could be helping to repair an issue with a line so production can resume.

Frontline leaders likely have a desk somewhere in the facility, but they rarely sit at it. Sometimes leaders might need to share a computer at that desk. During my time working retail during college, I worked at a store that had four managers and two supervisors who all shared the same computer. If they wanted to pull a report or review some data, they had to wait in line. If content creators only create reports that must be viewed on a computer, they will never get used.

Decision Making on the Floor is Paramount

Frontline leaders are constantly making decisions when walking the floor. Restock orders, new material requests, and resource management are all made in real time. If you want to enable your user leaders to make data driven decisions, the data needs to be with them on the floor.

Now you might be thinking “Hey Dom! Laptops are portable. They should just carry one with them at all times on the shop floor.” And that is a good point – a laptop in hand is fairly portable. However, there is a risk factor. You need to find a solid surface to set the machine on so you can use it. You also run the risk of dropping it or setting it down and forgetting it.

This is where a mobile phone becomes the star. It is compact, powerful, and convenient. Properly managed, this will enable your frontline leaders to have mobile reporting with them wherever they go. And before you say anything about the risks of dropping or misplacing a laptop are the same for a phone, I will challenge you and say it is not. Ruggedized phone cases are easy to find and people tend to not misplace their phone. Even better, if a company has a bring your own device (BYOD) policy, your leaders can use their own device. They tend to protect it more than company issued hardware and don’t want to be without it.

Know Your Audience

One of the challenges of content creators is understanding the frontline audience. Like many creators, I love putting together flashy reports and combining data for deeper insights. This is great content for consumers who are looking to learn more from the data and have time to pour over reports.

Frontline leaders tend to be more focused on current operations. They want to review data to decide what needs to happen next. As a result, frontline leaders want reports that have key metrics and measures that fulfill the immediate needs on the floor. Instead of fancy reports that show how production relates to the relative position of Mars, they need to know how they are trending to plan and what they can do to meet it.

Now frontline leaders still care about the insights found in data. They still have access to computers and will want to perform a deeper analysis on their data. The key is making sure you have the right tool for the right job.

Now that you understand why I think mobile reporting is so important, let’s start building one out.

Create your own Mobile Reports

The first thing you will need to do is update your Power BI Desktop client to the February 2022 release. If you have not done so, you can download it here. This will allow you to use some of the new mobile reporting features that we will discuss in this article. After you have updated Power BI Desktop, open up an existing report hat you want to enable for mobile use.

Once you have your report finished, go to the view ribbon. From there, you can click on the Mobile Layout button to bring up the mobile view. You will notice that you will see a new blank canvas in the shape of a mobile phone.

Switching to the Mobile Reporting layout

While the canvas is blank, you will note that there is a pane on the right hand side full of visuals from your report page. We now can drag and drop visuals onto the mobile report canvas and adjust them accordingly.

As a result, you can optimize your existing report for mobile devices. Note that I did not add my table of top fifteen products sold. It is great info, but hard to read on your mobile device. We will make it available for consumers from their computers, but suppress it on a mobile device.

Take Mobile Reporting to the Next Level

You might be saying “Big deal! This has been around for a while! Why are you writing an article about this?!” But here is what I am loving about this update – we now have the ability to customize our visuals in the mobile format. When you select a visual on the mobile canvas, a new pane is available on the right side.

Think about this scenario – you have a bar chart but want to see what the actual values are. If you are on your computer, you can hover to discover what the actual amount is on the bar. But with mobile reporting, it is far more difficult to accomplish that. So to help consumers see what is happening, I can now add a data label without disturbing my original report.

To add data labels to by bar chart, I simply select it and expand the visualization pane. From there, turn on the data labels and customize accordingly. Note the new little icon that appears – the Power BI logo with a mobile device over it. This indicates that the visual is customized for the mobile experience.

Customizing the bar chart to use data labels

The best part of this whole thing is when I go back to the regular report view, the data labels will remain hidden!

Original report experience as seen on a computer

And just like that I have been able to optimize my mobile reporting experience while maintaining my original report! Amazing!

But Before You Change Your Mobile Reporting Experience…

Really make sure you know what you want to change! Once you break away from the original report configuration, you now have to update both the desktop and mobile report canvas. Even worse, there is no clear indicator that a report has been optimized for mobile users. As you consider your mobile reporting controls and lifecycle plans, you might want to consider options that ensure the mobile canvas is properly updated.

So have you built out any mobile reports? Do you have any great use cases for mobile reporting? If so, tell me in the comments below!

How do I connect to REST APIs with Power BI? (Part 4)

By Dominick Raimato

On February 17, 2022

In Power Query, Web Connector

In my last article, I shared how to scale a query by using a function in Power Query. However, I noted there might be some challenges with this approach and considerations you should keep in mind when connecting to REST APIs. Some of these considerations are simple and are similar to normal query optimization activities. While not as technical as other articles, I feel it is important to take some time to focus on potential challenges you might encounter when leveraging REST APIs.

Limits, Throttling, and Speed

When reading your documentation, you might see words such as “limits” and “throttling” and wonder what they are. You will build your report and everything will be fine until one day your refresh fails. In reviewing the refresh errors it will suddenly make sense what “limits” and “throttling” mean.

To ensure APIs are not overburdened, limits and throttling are employed to prevent abuse. This protects the endpoints and provides a reliable service. Understanding how to navigate them are essential to prevent your refreshes from failing as your solution scales.

What are API Request Limits?

API limits restrict the number of calls made in a day, week, or month. If you signed up for the Yahoo Finance API, you notice they have three tiers of membership. The free tier limits your calls to 100 per day. Because of how we built our function, you would have to limit your report to 100 stocks and only refresh it once a day.

Now there are additional tiers that you can purchase which increase your limits. The Yahoo Finance API offers tiers that give you 20,000 or 75,000 calls per month which might help accommodate your use case but requires an additional financial investment. But even if you increase your subscription, note that these tiers still have a hard limit. Meaning that you might need to keep purchasing a higher tier if you blow through your request limit.

To be a good steward of your subscription, you will want to regularly monitor your API usage and verify that you are not burning through your quotas too quickly. It never fails – you run out of requests just when you need the data the most.

What is Throttling?

APIs use throttling to protect endpoints from being heavily queried over a short period of time. Queries are limited to the number of calls made per minute, per 15 minute window, or per hour. This protects endpoints from rogue code that could degrade the service because of abuse.

With the Yahoo Finance API, our documentation states that there is a rate limit of 300 calls per minute. If you had a list of 1,200 stocks it would take at least four minutes for the refresh to complete. (1,200 stocks divided by 300 = 4 minutes) Just like how every API is unique, they manage throttling differently.

Last year I built a Power BI report that leveraged Google’s mapping APIs to get coordinates and street views of addresses in my town. Google enacted throttling to limit requests to 100 per minute. When refreshing my data, I hit my 100 request limit and the API paused until the full minute had passed. In doing so, I was able to proceed with the refresh. With 3,500 houses in town, it took 35 minutes for the refresh to complete. It was slow, but it worked great.

On the other side of the spectrum, the Microsoft Graph APIs have a limit of 10,000 calls per 10 minute period. When I ran over this limit, instead of the API pausing, I ended up receiving responses with a 429 error stating I reached the throttling limits. In order to manage this, I had to get a little more creative with my query to make sure I did not receive that error.

Impact on Refresh Time

Regardless of limits, you will need to consider refresh time when using REST APIs. If your primary data sources reside in SQL servers, this will be an adjustment for you. Normally you just cast a wide net with your query and bring in everything you can and work from there. With APIs, you want to narrow that scope to only what you really need. But with REST APIs, the refreshes tend to be slower. Adding some filtering will speed up your queries and make them more efficient.

If you are in a situation where you need a large amount of data from an API, you might want to consider leveraging Azure Data Factory or Databricks to incrementally replicate that data into a data warehouse instead.

Paging Queries

For this series of articles, our endpoint was fairly basic. It only brought back one row of data for each stock symbol. This was by design because our overall goal was to cover the basics. As you explore other APIs, you might find endpoints that bring back several rows at a time.

If you expand your horizon with REST APIs, eventually you are going to run into something called paging. Simply put, paging is the splitting of responses over multiple queries. Similar to limits, this is to prevent overburdening the endpoint with your query.

As an example, let’s say you are querying an endpoint that has 50,000 records. Paging has been enabled on this query and is limiting the responses to 1,000 records at a time. In order to receive all 50,000 records, you will need to build logic into your query to pull each page individually.

So how do you know your endpoint is using paging? It is all about the documentation. If an endpoint has pagination, there are usually a few parameters that will tip you off. If you see a parameter called “PageNo”, “PageSize”, or “Top”, you could run into paging.

It is also important to consider that you could avoid pagination with quality filtering. If your request is targeted and removes the noise of other records, it could avoid this problem all together.

In the coming weeks I will be doing an article specifically on paging, so keep an eye out for it!

Token Authentication

Another challenge you might encounter is token based authentication. Instead of passing an API key for authentication, you would need to call another endpoint to receive a token. Once you have the token, you would pass it through a header.

If you encounter this situation, you would need to create a function in Power Query that would solely return a token. While it takes a few extra steps, it is likely reusable with other endpoints in your report. In addition to that, it will simplify your query code and keep it tight and compact.

Conclusion

Congratulations for making it to the end of this series. There are a lot of challenges when implementing REST APIs as a data source but the juice is worth the squeeze. Taking yourself out of the mix and automating my refreshes is the end goal. And while the experience may not be as efficient as a SQL data source, it still gets the job done.

Have you been following along? Were you able to successfully connect and extract data from Yahoo Finance? How about other REST APIs? Let me know in the comments below!