Entering the (Power BI) Matrix – Part 4: Granularity Mismatches

…don’t worry about the vase. – Oracle
What vase? [Turns around to look for the vase and breaks it]. – Neo
That vase…
What’s really going to bake your noodle later on is: would you still have broken it if I hadn’t said anything? – Oracle

In this case, for our story, the Oracle has predicted how much data the Hoverships will extract each month. But she hasn’t predicted how much each crewmember will extract, or how much on each day of the month.

There are a variety of approaches to handling this disparity: It’s possible to divide the less-granular projection up into days of the month, as well as by members of the crew. But this approach has inherent assumptions that aren’t necessarily true. And most of the time, it’s best to leave our assumptions behind and let the data tell it’s own story, unencumbered.

So, instead of kludging the data into alignment with arbitrary assumptions, I’m going to borrow a pattern of logic from the rockstars at SQLBI.com. And if you haven’t already – you really owe it to yourself and to the consumers of your reports to run out and buy their book: Dax Patterns 2015. Until your copy arrives: the excellent article they’ve written on the subject is also found here.

In a nutshell: We have one table of performance data, granular to the day and crewmember. Another table of prediction data that is granular to the month and the vessel. Aligning the two will require some basic data modeling.

The entire simple schema for our dataset looks like this:

We have a common date dimension table that populates the date fields on the rows or columns of our matrices. This table is joined to our performance fact on the actual date. It is joined to our oracle fact on the first date of the month.

We have a common ship dimension, which is indexed and joined to each fact table on a key column.

There is also a crew dimension table, which is joined to the performance fact.

The ship and crew dimensions are joined with a bidirectional relationship on the ship_id. Bidirectional filtering has significant impacts (kudos once more to the SQLBI.com team for the excellent expansion, here), both on performance and logic, and should be used sparingly. But in our case, it is useful to allow ship selections to filter crew and crew selections to filter ship.

Without writing any logic, attempting to align the performance and the prediction data would return the following, which is correct when aggregated at the month and ship grains:

However, when we drill down to the daily grain or to the crew grain, we expose problems.

On the date grain, the monthly prediction is only aligned to the first of the month. For all other days it is null. And on the Ship/Crew grain, the prediction repeats (because there is no relationship) for the Crew.

In some use cases, this behavior may be tolerable. In others it will not be. And for those, we’ll need some DAX to clean it up.

DAX is heavily dependent on context, which is beyond the scope of this article. For an overview, I suggest this article from SQLBI.com. What context our DAX expressions will be evaluated in depend entirely on the dimensions we have used to construct our matrix, static filters, active slicers, and interactions between visualizations. And DAX provides functions for reading and applying conditional logic on the context of a given expression.

In this case, we’ll be leaning on the ISFILTERED() function, which returns TRUE when a given column is directly filtered in a given context. That directly is key here – another function ISCROSSFILTERED() will return true when a column is filtered by another column. But in this case, we are only interested in whether our column is directly filtered.

Including that column on the dimensional hierarchy of our matrix and drilling down (or expanding to) that dimensional grain will cause it to be directly filtered by the matrix itself, in order to separate the members of that dimension.

The logic is written like this:

Click to Copy

predicted variance output =
IF (
NOT ( ISFILTERED ( dim_date[Date] ) || ISFILTERED ( dim_crew[Crew] ) ),
[predicted variance display]
)

Which performs the following:

• If the Date column is directly filtered, or if the Crew Column is directly filtered, return null()
• If neither are true, return the output of the [predicted variance display] function,
• Which is just a version of our variance function from the second installment, formatting the output of the variance between the actual extract and the oracles prediction.

The output of this function will only return the predicted values at the proper granularities in the table, like so:

In the ship>crew hierarchy, the value is only displayed at the ship and total levels.

In the Year>Month>Date hierarchy, the values are only displayed at the Month, Year, and Total levels.

It’s possible to get a great deal more inventive with this function, and return various outputs for various granularity levels, instead of just returning blanks for mismatches. But for the sake of the Nebuchadnezzar’s reporting to Zion, and for our example here, this does nicely.

Proper data modeling coupled with this dax function can solve for nearly any difference in granularity between different data sources in your dataset. But, as you can see in the examples above, this technique still results in blank columns when the data isn’t applicable. Unfortunately, if you’ve spent any amount of time working with the tool, you know full well: that’s one limitation of the Power BI Matrix.

Or is it? We took the red pill, remember? This rabbit hole goes all the way down.

What if I told you that even that much was wrong. That columns can be just as dynamic as rows, and that we never have to display unuseful output on either axis. That it’s entirely possible to build our matrix in such a way that it is flexible enough to output only the dimensions and measures populated for ANY subset we wish to select.

Up Next, we’ll find out: Dynamic Everything, and Disconnected Tables.

Read more from our Data Visualization practice here.