Cool stuff with the Power BI matrix visual

At some point in time, you will encounter a use case and think: 'well, this can only be done with custom visuals....'.

However, I like to use the standard capabilities of Power BI. And I see these occasions as a challenge to get them done in standard Power BI anyway. It might not always yield the best visual end result, but that's not the goal here. Goal is to get things done without using external tools, and learn from doing so.

For this use case, I wanted to visualize the changes of project launch dates between a certain point in historic time and actual, live data. Next to that, there was a strong wish to group per segment, indicate if it was an application or innovation project, the phase of the project, and the peak net sales value. Let's not discuss the data in detail, but basically the launch date at a certain point in time can be selected with the slicer at the bottom left and it is visualized versus the currently planned launch date. Goal is to 'keep an eye on the progress' and to check if people are not sneakily shifting deadlines. (for example)

Of course you could simply put it all in a standard table, one column 'historic launch date' and one column 'current launch date', potentially even a delta. But where is the fun in that? So I used my trusty excel sheet to sketch a wish and wondered.... could I do something like that using standard Power BI visuals?

Well, yes. Because the matrix visual nowadays is much, much more flexible then it was before. The (unpolished) end result is shown below. There is also a nice mouse-over tooltip that shows the launch dates over time per project, but lets leave that out of this topic.

Once you know how to set it up, it's remarkably easy and it will open up some new possibilities for sure. So how is it done?

• For the column headers, start with a disconnected table. The order_h1, apart from the top 3 rows, gets a consecutive index or is simply the year number. The order_h2, apart from the top 3 rows, needs to be equal to the month number. When outside the query editor, sort Header_1 on order_h1 and Header_2 on order_h2. Add both headers to the matric visual and expand them.

• For the row headers, simply drag into the matrix anything you like, as long as you go to the detail level that you need. In my case: projects.
• Now the fun starts. In order to tell what PowerBi needs to show in each filtered context 'cell' of your newly created matrix of size {projects x header_2} we need to make a dax measure. In my case, I made one measure on what to display to the user and one measure on the background color. You can also make one with foreground colors.
• You can use the switch function to find the part (context) you are currently in, and based on the logic of your own choice, you can fill the cells as you please. In my case, I filled the status, portfolio and the value in the top part of the code, and if the header_1 is not 'project info' then it must be a column on the 'calendar' range. That second part basically checks: what was the date before, what is it now, what is the delta? And then with this information you can determine per 'cell' what you would like to display just as you did with the status, portfolio and value.

If you look to the DAX code, you will notice that I used a lot of silly icons for the sake of the example. However, these are normal text icons. I tried using real conditional icons instead but it does not work that well, as PowerBI reseves a square whitespace on each field and you cant selectively say per column of the matrix 'icon only'. To choose an icon, push windows + . (period)

Setting the background of the matrix fields works in a similar fashion, but instead of indicating the text to display, we prescribe which color to take. For example, the switch statement will contain something like the snippit below. To help you pick the right color, use the hex color picker embedded in google search.

To implement the background or foreground formatting, set conditional formatting to the measure of your matrix. Choose background / foreground, format by field value, and select your background color measure. In case you cannot select it, make sure that all possible outputs of your background color measure are hex colors. ( or blank() )

I really liked doing this exercise and to me it demonstrates that PowerBI is getting more and more able to get the things you want done without having to revert to third parties. Yes, you need to do some concessions in some cases still, but we're getting there :)

Thanks for the read! If you have remarks/comments please let me know

Dennis