Today Microsoft released Power BI Desktop March 2020 which I was hoping that it includes a simple feature on Matrix visual to be able to sort the Martix by column in descending order, but, it doesn’t. So, in this post I quickly show you how to sort Matrix by column in descending order.
Here is the scenario. One of my customers is building a report in Power BI showing sales by Year, Month and Day of Week in a Matrix as below.
Sorting Matrix Visual in Power BI
Everything looks fine! But looking at the Matrix sorting quickly reveals that such feature is NOT available (YET). But the customer would like to see the Matrix sorted by Year in descending order, something like this.
Sorting Matrix by Column Headers in Descending Order
This post has been waiting in my blogging list for a while and now this is my last post in 2019. I wish you all have a wonderful year ahead.
In this post I discuss a very important aspect of data visualisation; Colour Coding. I believe, colour coding is one the most powerful and efficient ways to provide proper information to the users. We learnt as human being that the colour can tell a lot about things. For instance, we look at green grass, if it is light green we immediately understand that the grass is quite fresh and healthy. When she gets a bit yellowish, we know that she’s perhaps thirsty. When it gets brown it is probably too late.
Another perfect example is traffic lights. When it is green, everyone is happy, when it is yellow, everyone is racing to pass the junction, well, I’m just kidding, some people tend to pass the yellow light while everyone knows they have to stop when traffic light is yellow right?? And… when it is red, we have to stop. Enough saying about colour coding and its affects on our lives on a day to day basis. Let’s talk about colour coding in Power BI and quickly get to more exciting stuff.
So… colour coding in Power BI, well, we could colour code from the day first that Power BI born, but, perhaps not in a way that I’m going to explain in this post. Conditional formatting is also around for a while now. In this post I show a technique that we can implement in Power BI to use a consistent colour coding across the whole report.
Here is a report without colour coding:
Power BI Report without Colour Coding
And now look the same report that is colour coded:
Colour Coded Power BI Report
Let’s get into it.
Getting Started
In this technique we’ll follow the steps below:
We jump online using some awesome free colour palette websites to generate the colours we’d like to use in our reports
We copy the HEX values and paste into Power BI (via Enter Data)
We define a range of numbers to identify the ranges that our values will fall into. I personally use percentage, but it might be something else in your case
We then define some measures to pick a specific colour for the measures we want to colour code
When you work on real-world projects in power BI, you would probably have different environments Like DEV, UAT, Pre-Prod and Prod. It is important for you and your audience to know what the data is coming from. Am I looking at Dev or UAT data or I am actually looking at real data in Production environment. You may have asked or been asked with a question like “Where the data is coming from?”. It is important to know how trustworthy the data you’re analysing is. In this post I show you an easy way to show the environment your Power BI report is connected to.
How It Works
To display the environment name you use query parameters, then you reference that parameter, turn it to a table and add columns to show the environments accordingly. Easy right?
Depending on your scenario the implementation might be slightly different, but the principals are the same. In this post I use a SQL server database. Therefore I need to Parameterise server name. in real world you may also need to parameterise the database name. Again, if your case is quite different, like if you get data from Excel, then the Excel path can be different for different environments. Let’s dig-in.
Open Query Editor
Click “Manage Parameters”
Click “New”
Enter “Name” and “description”
Tick “Required”
Select “Text” in “Type”
Select “List of values” in “Suggested Values” and type in server names for different environments
pick a “Default Value” and “Current Value”
Click OK
So far you created a new parameter that can be used to get data from a SQL Server data source.The next steps show you how to use that parameter to show the environments in your reports.
Power Query (M) made a lot of data transformation activities much easier and value replacement is one of them. You can easily right click on any desired value in Power Query, either in Excel or Power BI, or other components of Power Platform in general, and simply replace that value with any desired alternative. Replacing values based on certain conditions however, may not seem that easy at first. I’ve seen a lot of Power Query (M) developers adding new columns to accomplish that. But adding a new column is not always a good idea, especially when you can do it in a simple single step in Power Query. In this post I show you a quick and easy way to that can help you handling many different value replacement scenarios.
Imagine you have a table like below and you have a requirement to replace the values column [B] with the values of column [C] if the [A] = [B].
One way is to add a new conditional column and with the following logic:
if [B] = [A] then [C] else [B]
Well, it works perfectly fine, but wait, you’re adding a new column right? Wouldn’t it be better to handle the above simple scenario without adding a new column? If your answer is yes then continue reading.
