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.
Rob Collie takes us through a good use of FILTER() in DAX:
The thing both of those formulas have in common is that they are using a measure in the filter argument of the CALCULATE function. In both examples here, I’ve highlighted the offending measure in yellow.
CALCULATE([Sightings per Year], [Avg Sighting Length in Mins]>6)
CALCULATE([Sightings per Year],
Observations[TTL Min]>[Avg Sighting Length in Mins])In the first formula, I was trying to use a measure on the left side of the comparison, and in the second, I was trying to use a measure on the right side of the comparison. Both are illegal.
Read on to see why and how you can use FILTER() to solve these problems.
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.
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.
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.
Rob Collie explains the power of SUMX() in DAX:
Have you ever written an array formula in Excel? (Don’t worry, most people haven’t). Have you ever written a FOR loop in a programming language? (Again, don’t worry, there’s another question coming). Have you every repeated something over and over again, slowly building up to a final result?
That’s what SUMX() does. It loops through a list, performs a calc at each step, and then adds up the results of each step. That’s a pretty simple explanation, but it produces some results in pivots that are nothing short of spectacular.
Read on for a few examples.
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.
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.
Kevin Feasel
2019-07-23
Power BI
No Comment