Entering the (Power BI) Matrix

| Last Updated August 10, 2021 - by corey m turner under data visualization

What if I told you there was a way to easily present your data — from any source — at any level of detail you can imagine? If you want this future of endless possibilities, then consider this post the red pill – it is time to enter Power BI’s matrix.

Power BI’s matrix isn’t a waterfall of green on black. Thankfully. But it does allow you to quickly and easily construct adaptable views on your data that you wouldn’t be able to produce, otherwise.

But in order to get to the bottom of it, and get the most OUT of the matrix, you’ll need to see a bit of what the matrix actually is.

And I can’t tell you what the matrix is, can I? You have to see the matrix for yourself.

At it’s core, the Power BI matrix is simply the same functionality that Excel offers in Power Pivot. The two products utilize the same engine, and the same query language, DAX.

So if you’ve used Pivot Tables, and understand how they work, the matrix should be very familiar to you. If not, then at its simplest it’s just a matter of placing dimensions on the rows and columns trays, and then your measures values tray, and the matrix will produce a tabular array of all combinations of the dimensions you provided, and the measures corresponding to each.

But where the matrix really shines is in the ability to allow you to roll-up and drill down through the dimensions you’ve placed on the horizontal and vertical axes. You can get aggregated results at ANY granularity that you’ve included in your dimensional hierarchy, either for the whole array, or for the individual branch of the tree that you want to explore.

But even as powerful as it is, the Matrix does have some limitations, at least before you start getting under the hood a bit. You’re going to need some Kung-Fu, if you are going to survive in the Matrix.

Over the course of this linked series of blog posts, we’ll be exploring methods for dealing with these limitations. Think of it as your own quick download of the techniques you will need here.

In the next several posts, we’ll explore:

Ways to dynamically scale units at the row level, instead of having to stick with a static scale for an entire column.
Ways to handle exceptions for values that are out of expected range.
Ways to handle output in nonstandard formats, such as time duration across time scales.
Ways to include indicators or other graphics in matrix output.
And ultimately, an approach to dynamically adjust the matrix to display only the columns and rows present for any dynamically selected slice of your data.

Read more from our Data Visualization practice here.