Category: Power BI

Matt Allington shows how you can now use icons as the output of conditional formatting in Power BI:

Note how the icons above have both shape and colour so you can differentiate between them even if you are colour blind.  This is best practice.

You can also change the default formatting to work on the hard coded number settings that you specify. In the example below I have changed the settings to work on absolute numbers instead of percentages (note the changes in the highlighted boxes).  Also note that I have set the minimum and maximum numbers shown as 1 and 2.  To do this, simply delete the value in these boxes.  Thanks to Chris Webb for finally helping me understand how this works.

It’s easy to go overboard with this, but I’m happy to see conditional formatted icons in place; done right, you can pack a lot of information into a small space with them.

Imke Feldmann has a new M function for us:

While there is a native function to fetch text between 2 delimiters in Power Query, there is no such function that removes the text instead. Therefore I’ve created a custom function Text.RemoveBetweenDelimiter. It even lets you choose to remove the delimiters themselves as well via the optional 4th parameter.

Click through for the script and sample uses.

Matt Allington shows how you can build up a Pareto chart in Power BI:

Pareto Analysis is a statistical technique that applies the Pareto Principle to data. This is more commonly known as the 80:20 Rule. The Pareto Principle is based on the presumption that a relatively small number of inputs (20%)  have most impact on the results/output (80%).  The 80:20 rule can be applied to a wide variety of data in most businesses. 

Examples include:
– Which 20% of products make up 80% of sales
– Which 20% of customers make up 80% of profit.

Pareto analysis is a rule-of-thumb technique but it does provide reasonably useful results much of the time.

Chris Webb explains what happens when a selected value on a slicer disappears in the underlying data set in Power BI:

There is a slicer on the left with five items in it, a table showing the actual contents of the table (I’ve disabled visual interactions so the slicer doesn’t slice the table) with five rows and a card showing the output of the following measure:

Selected Number = SELECTEDVALUE(MyNumbers[Column1], "Nothing Selected")

In the screenshot above you can see I have selected the value 78 in the slicer and the measure – as you would expect – displays that value.

Now what happens when you refresh the dataset and the table contains a different set of numbers? 

Read on for the full explanation.

Gilbert Quevauvilliers shows how you can add line breaks to DAX measures for formatting results in Power BI:

I was at a customer and they were drilling through between pages, and I wanted to put in what the filter selections were when they drilled through to the new page.

This would allow them to easily see what had been on the previous page, and to avoid going backwards and forwards.

I quickly created the DAX measure to show the filters, but one thing that bugged me was that it looked a bit clunky and not clear in terms of what the filters were.

I knew that I wanted to use a Line Break to put each filter on a new line. 

Read on to see how you can do this.

Kasper de Jonge shows how to enable scheduled refresh on custom connectors in Power BI:

Here is a post that is long overdue as the functionality has been available for almost a year now. Today we will enable scheduled refresh to the Strava custom connector I build before.

The trick to this is that we need to extend the code of the Strava connector with a “TestConnection” function as is described here. This will allow the Power BI gateway to know how to test the connection.

There’s just a little bit of code involved.

Matthew Roche (with an assist from Imke Feldmann) shows how you can check if a value—or part of a value—is numeric in Power Query:

Using Imke’s approach, this is what I ended up doing:

1. Extract the last two characters from the source column
2. When the extracted characters contain a number, convert them to numeric
3. Standardize the resulting value to represent the correct year

Read on to see how Matthew pulled it off.

Matt Allington shows how you can create a combination slicer and histogram in Power BI:

I was doing some online shopping last week and saw a price slicer with a histogram to show the concentration of price points.  I thought it was pretty cool, so decided to see if I could build this in Power BI.  I came up with a solution that works, and in this article I show you how you can do it yourself.

Click through for instructions and a video.