DAX Blog Posts

Do your DAX measures return interesting values but not the numbers you want? If the answer is yes, and it happens to all of us, then the problem probably lies with the filter context in which the measures have been calculated and the fact that you haven't correctly identified them. When a DAX measure is calculated in the "Values" bucket of a Power BI visual, whether it's a column chart, pie chart, table , matrix or indeed any other visual,… Read More

"Why does the world need another DAX book ?" was my first question to Alison when she told me she'd written guide to the DAX language in Power BI. "Well, I've written the guide that I wish had been around when I was first learning DAX, aimed at Excel users of varying levels of ability". Was her reply. "You see many Excel users would struggle to construct an "IF" formula, let alone know what VLOOKUP stands for. These are often… Read More

In DAX there is a special way to identify null or empty values and that's by using a value called "blank". In order to return blank values, we can use the BLANK() function e.g. 10 Percent = IF ( Winesales[CASES SOLD] > 100, Winesales[CASES SOLD] * 0.1, BLANK () ) When constructing DAX expressions using IF, if you want to return BLANK() on the "Value if false" argument, you can just close off on the bracket because BLANK() is the… Read More

I thought this would be an easy blog. Not much to say about using Power BI's Conditional Formatting. All it does is make visuals look more appealing by using pretty colours based on the data's values. How wrong I was! In fact there's so much to say that I've had to resort to a mini contents page using outline numbering and links to the relevant paragraphs. In fact, just like the old days of writing user manuals! So here goes… Read More

We've often been asked if it's possible to group the results of a DAX measure into numeric ranges. For example, to group a Total Sales measure for each Customer into various numeric ranges, sometimes referred to as "bins". The answer is, yes it is, as you can see from the screenshot below:- In this screenshot, we can see that we have five customers whose Total Sales fall between 150,000 and 199,999 in 2019. How did I do this? Firstly, my… Read More

Recently I was asked by one of our clients to solve this question; how could they calculate the number of days between customers' transactions? Clearly, in order to do this calculation, you would need to find the transaction date that was previous to the transaction date sitting in the current row. You could then subtract the earlier date from the later date and return the days between them. It occurred to me, that such a calculation could have a more… Read More