skip to Main Content

Today I have decided to stray from my normal topics to talk about two exciting updates in Power BI Desktop this month. March 2017 really was a great release, but the 2 things I am most excited about are the new Matrix preview and Custom Report Themes (color palettes) also in preview which are amazing New Power BI Features.

The New Matrix (preview)

The capability of the matrix visual has been a big gap for a very long time (since the beginning actually).  The current matrix barely meets the minimum capability to make it usable.  It is fine if you want 1 item on rows and 1 one columns, but it falls away very quickly thereafter. All that changed last week with the delivery of a new matrix preview.

Turn On The Preview

Microsoft often releases functionality that is not quite finished but is good enough to let it loose on the community. All these preview items can be turned on/off in the preview settings window. You can find the preview features window at File\Options and Settings\Options.


Once you set the options, you will need to restart Power BI Desktop for them to take effect.

After turning on the preview, you will see there is an additional matrix visual.


I like this approach as it means anyone using the existing basic matrix can continue to do so without breaking anything while Microsoft builds out the new features.

Expand, Collapse, Drill and Filter

Expand and collapse behaves just like a pivot table however with a slightly different UI. The new matrix experience is however entirely consistent with the chart drill experience so it is very intuitive.

The new cross filter behaviour is of course not possible in a regular pivot table in Excel (without VBA). You can select any column, row or cell in the matrix and it will cross drill the other visuals on the canvas as can be seen above.

Staggered Column

The staggered column formatting is is a nice touch allowing the user to change the offset of the nested columns as you can see below.

Custom Report Themes

The second killer feature this month is custom report themes.  I’ve worked on developing custom software and solutions for many years. In my experience custom color palettes can be a deal breaker for some users. Custom colors won’t help you improve the quality of the analysis but if you don’t let the users have their corporate colour palette, then you will be on a hiding to nothing. I figure just let them have the colors they want and move on.

Custom report themes are also in preview and still have a way to go before they are fully integrated into the UI. But thankfully Microsoft has fast tracked the preview to allow those that need these to get started.  You can turn on this preview feature the same way as before with the matrix.  After you turn on the preview feature, you will be able to import your own custom theme as shown below.


But before you can do that, you need to create your own theme file.

Create a JSON Configuration File

To use the preview feature, you need to create a JSON configuration file that contains the color codes you want.  This is not very user friendly in its current form, but that is the price you have to pay to get the feature more quickly as a preview feature.  It is pretty easy to create one – I just did it using Notepad.exe.  Edit:  check out Chuck Sterling’s Palette tool here

It is also very helpful to get a screen colour palette tool to help you. There are many on the web that you can use – I used this one to find something l liked.

If you already have a corporate palette then you really just need a way to accurately select the correct color hex codes (eg using a screen color picker tool) – I use FastStone Capture.  If you want to build something from scratch, then a web based color palette tool can be your friend.

Here is the content of the JSON file I created








I just added the above code to a new NotePad file, then saved this as a text file with the name Excelerator.json (make sure it doesn’t add .txt to the end by first selecting file type *.*) and then I imported it to my Power BI Desktop file using the menus I showed above – it worked first time.


When I went to select a color thereafter, here is the palette I had available.


OK, I admit I am not a graphic artist and this first attempt isn’t great.  One really nice thing about the theme feature is that you can specify only a few colors or as many as you want (literally hundreds and hundreds).  In my example above I only specified  2 colors (the 2 light blue columns of colors), and Power BI filled out other possible colors automatically for me across the screen (you can see 6 additional color families).  Also all the color variations down the length of a column are automatically generated by Power BI.

OK, so I didn’t like what I had, so I went back to my color palette tool and tried again.  This time I got something softer on the eyes (less electric).  But of course the whole point is you can have what ever you like.


Here is my final JSON file








Ironically I started a new project with a client today and these 2 features were on the “I hope they arrive soon because we really need them for this project” list. Perfect timing Microsoft!

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here.

This Post Has 27 Comments
  1. Great post Matt, two great and much needed adds to PBI. Now let’s get some more flexibility w which sub totals show (including a grad total), maybe throw in the classic view? and we could almost name this thing ‘pivot’ 😉

  2. Great article Matt, as usual 🙂
    I hope Microsoft will add soon the ability to sort any columns by asc/desc order in the new matrix visual (which is clearly a REAL game changer as you mention!) as it is the case for the classic old one.

        1. Hi Matt,

          another question : what do you thing about the ability for PowerBI to import viz into MS Word ? Indeed, often we have to write some documents and to insert some viz… ?

          1. Well it is just my opinion – others may feel otherwise, but I don’t see a lot of value in developing this. I see value in interactive PowerPoint integration and possibly static PowerPoint slide deck creation, but I don’t see broad use for a Word based equivalent. It is pretty simple to take a screen shot into the clipboard and paste it into Word. If you had more images that you needed, you could create a PowerPoint pack and then copy the images from there. JMO

  3. Hi Matt,
    Any idea whether these features will be added to the Power BI service or is it exclusively for Power BI Desktop?

    1. I haven’t tested it, but I assumed it was already there. Have you tried? If not, my guess is “weeks, not months”. Normally you can author in Power BI Desktop and publish to the service straight away. Somethimes editing a native visual in the service may take a week or so before you can do it.

  4. Hi Matt
    Great post on the new matrix…
    Just a thought… Is there a way to link a PBI model to Excel….reason being to allow Excel to generate a pivot table on PBI data.
    Thanks, Fran

  5. When I try to import a theme, not all the object colors change. Are there more propertries on the JSON file we can assign?

    1. How many colours are you importing? I have only done a few in my sample. But you can bring in many (probably hundreds). If you only bring in 2, the application will make up the rest.

  6. Hi Matt, thank you for the post. It’s been quite some time and from what I can see the staggered columns feature in options and preview settings are no longer available? Do you have any other suggestions to achieve a typical pivot style table with numerous columns and rows?

    1. Hi Allison, the reason the staggered columns are no longer available in preview is that it is now deployed as standard. Same features exist, just no longer in preview. Thanks for letting me know.

Leave a Comment or Question