A typical problem with data that has been created by manual entries is that category values are often misspelled or missed. So in this article I’m showing a very powerful technique on how to deal with this problem to clean up dirty category data. It was inspired by the “Preppin’ data” challenge whose instructions you can read here.
Categorize dirty data
In this article I show methods to calculate the doubling time with DAX in Power BI. Doubling time is an indicator used for exponential growth scenarios. It indicates how much time it takes for a figure to double.
You might have come across it in studies covering the current COVID-19 epidemy like here for example. In there you see how many days it took for cases to double. But these figures are shown as snapshot of today and I think it’s also helpful to see their development over time. With a bit of DAX we’ll get there:
Doubling time in DAX (using small multiple visual from Daniel Marsh-Patrick)
Low values mean high speed of growth, so the bottom area is the danger zone here. I find that a bit unusual and thought about displaying it the other way around with negative numbers instead: Read more
When you’re dealing with a beast like DAX you can use any help there is, right? So here I show you how you can debug DAX variables who contain tables or show the result of multiple variables at once. So you can easily compare them with each other to spot the reason for problems fast.
Please note, that currently only comma separated DAX code is supported.
Watch this measure from Gerhard Brueckl’s brilliant solution for dynamic TopN clustering with others. It contains 5 variables who return tables and one variable with a scalar:
Measure with variables who contain tables and scalars
If you want to follow along how this calculation is evolving for each value in a matrix, my VarDebugMeasure will show details of every variable like so:
Measure to debug DAX variables
CALCULATE is the most powerful function in DAX, as it allows you to change the filter context under which its expression is evaluated to your hearts content. But with big number of options to choose from, often comes big frustration when the results don’t match expectations. Often this is because your syntax to modify the filter context doesn’t do what you’ve intended. Unfortunately CALCULATE only displays its result and not how it achieved it, so debugging becomes a challenge. This is where my CALCULATE Debugger measure can help out:
This is a measure that returns a text-value, showing the number of rows of the adjusted filter context table, the MIN and MAX value of the selected column as well as up to the first 10 values. Just place this measure beneath the CALCULATE-measure in question and try to find the error 😉
!! This is a clickbait post to get your vote for some missing features in Power BI !!
Although this might not be what the inventors of Power BI had in mind, large lots of folks are trying to create classical financial statements in it. And putting aside the afford that might go into getting the numbers right, there is still a major drawback to swallow:
Today I want to share a scenario where a running total calculation in the query editor saved a model that run out of memory when done with DAX:
The model couldn’t be refreshed and returned out of memory error with a calculated column in the fact table of over 20 Mio rows (from a csv-file). A running total should be calculated for each “JourneyID”, of which there were over 1 Mio in the table itself. This rose memory consumption during refresh by over 300 % – until it finally errored out:
Besetzung =
CALCULATE (
SUM ( Fact[Entries] )
– SUM ( Fact[Exits] );
FILTER (
ALLEXCEPT ( Fact; Fact[JourneyID] );
Fact[StopId]
<= EARLIER ( Fact[StopId] )
)
)