Entering the (Power BI) Matrix Part 1: Dynamic unit scaling in DAX
Unfortunately, no one can be told what the Matrix is. You have to see it for yourself.
At first glance, it’s easy to get the impression that the Matrix functionality in Power BI is just a tool for numeric tables. After all: No matter how the columns and rows are structured, at some point there needs to be something on the values shelf. And that’s always a number, right?
Well, as it actually turns out: No. That’s not what the Matrix is. Not at all.
Instead, Power BI’s matrix functionality is unparalleled in the ability to build out a complex hierarchy of dimensional rows and columns, and perform aggregate logic against any almost combination of those dimensions.
And for those coming from Tableau backgrounds, this is important distinction from Tableau’s crosstab functionality. Tableau depends heavily on table calculations, which perform logical operations on the tabular output of the crosstab.
Whereas the Matrix (and Power Pivot) instead utilize a robust set of logic to perform operations against Power BI’s far more robust data model.
In the Power BI Matrix, the output is the end PRODUCT of our logical operations. We won’t need that output to be useful for further operations, ever, at all.
So our output can be text. And DAX provides us a robust set of functions for manipulating text. Which means that suddenly, our (Power BI) Matrix is far more flexible, and powerful, than it seemed at first glance.
One use case we often encounter is unit scaling: where a calculations output ranges from single digits to trillions depending on how it is sliced. The consumers of the report don’t want to see all the digits of precision in the higher output ranges,
Instead, they prefer formatting into K,M,Bn,T at the higher ranges. But they also didn’t want to lose detail when slicing the data into smaller slices, such as daily or other smaller grains.
If you’ve looked into the native matrix functionality options, you’ve probably concluded that this isn’t possible, at least not within the GUI. The native formatting options for the column allow for scaling the units, but only for the entire column. This does not scale each row individually. These limited options are shown to the right:
But we have DAX. Which is our red pill, here: exposing whatever functionality we need behind our Matrix. And with a little Kung-Fu, what you once might have thought was impossible is entirely within our reach.
I’ve had some fun constructing a sample dataset/model for this series of posts, based on some data extracted from the Matrix Fandom Wiki. There’s a table of hovercraft and their captains at https://matrix.fandom.com/wiki/List_of_hovercraft. I scraped this data and built a rudimentary table providing Craft, Captain, and crew. The wiki says that the events of the Matrix Trilogy occur in 2199. Using Power Query it’s trivial to create a date table populated with 10 years of days beginning Jan 01 2199.
Crossjoining the Craft/Crew table with the date table provides a bare framework for us to work with. From there, a series of random values are generated which enable us to build a sample dataset procedurally without any data entry outside the tool.
And for the sake of this post, I’ve imagined that the crews of these Hovercrafts have been tasked with extracting data from the Matrix as they roam the ravaged landscapes of The Matrix’s Earth 2199.
For each day, for each member of the crew of each of these craft, I’ve generated a random number, signifying kilobytes of data extracted from The Matrix. Power BI’s integer data type is Int64, which can handle values of up to 9223372036854775807.
To leave some room for aggregation, I’ve scaled my random numbers from kilobytes (2^10 bytes) to the exascale (2^50 bytes). To make it more interesting, I’ve allowed for negative numbers as well. Think of this as viral corruption due to contact with enemy Agents in the Matrix. It’s more fun that way.
Outputting these values without scaling gets ugly, immediately. No consumer of any reporting EVER wants to count and compare up to 19 digits per row. We need to make this prettier, and easier. We need to scale this output.
Additionally, bytes aren’t scaled base 10, though we routinely express bytecounts as X.XXX at a given scale. They are base2, with each increment of the 10th power being 1024 times larger the preceding scale.
We can easily account for both with the following DAX code:
Click to Copy
kbytes_display = VAR kilo = SUM ( fact_matrix[kbytes] ) RETURN SWITCH ( TRUE (), ABS ( kilo ) > 2 ^ 50, FORMAT ( DIVIDE ( kilo, 2 ^ 50 ), “###.00 Eb” ), ABS ( kilo ) > 2 ^ 40, FORMAT ( DIVIDE ( kilo, 2 ^ 40 ), “###.00 Pb” ), ABS ( kilo ) > 2 ^ 30, FORMAT ( DIVIDE ( kilo, 2 ^ 30 ), “###.00 Tb” ), ABS ( kilo ) > 2 ^ 20, FORMAT ( DIVIDE ( kilo, 2 ^ 20 ), “###.00 Gb” ), ABS ( kilo ) > 2 ^ 10, FORMAT ( DIVIDE ( kilo, 2 ^ 10 ), “###.00 Mb” ),
IF ( ABS ( kilo ) <> BLANK (), FORMAT ( kilo, “### Kb” ), BLANK ()) )
A quick word about SWITCH(TRUE()), first though: This is simply a (vastly superior) alternate construction to nested IF statements. Since SWITCH() normally requires exact comparisons for each case, we just move the operator to the matching value, which returns either a TRUE() or FALSE() to compare with the original TRUE() value. It’s a little counterintuitive, but it works, and it cleans up DAX code considerably.
Credit to the team at powerpivotpro.com for this excellent post on switch(true())
This code performs the following:
This function yields output that scales the native sums to the following in a matrix.
And there we have it – custom scaling across multiple scales independently by row. The values scale from thousands to billions in the same column, both positive and negative.
And here’s the sample Power BI report page built on this logic. A download link (for every post in this series) will be provided at the end of the series:
One last thing, before we chase this rabbit hole any further:
It is important to remember: Though we won’t need numeric values on our Matrix, we will need them for operations in our logic, and for use on other visuals. So it’s important to make sure there are both value and display versions of each calculated metric you set up.
Over the course of this multipart series, we’ll be building towards an ultimate objective of a dynamic DAX framework for our Matrix which can handle any value, at any grain, and dynamically respond to the data to present only salient columns (and rows) for any given slice of our dataset.
Next up: we will tackle DAX logic for formatting rates and other derived fields, including exception handling for calculation results that are out of expected or useful range.
Read more from our Data Visualization practice here.
