DAX Blog Posts

"Nothing in life that's worth anything is easy." Barack Obama. And we could also say that nothing in DAX that's worth anything is easy! Certainly the concept of context transition is one of the more challenging concepts to get to grips with in DAX. It can't be explained in a few short paragraphs so I apologise in advance for this rather long blog but please stick with it and read on because once you understand this concept, a whole range… Read More

I recently listened to a thought-provoking blog from Alberto Ferrari (https://ssbi-blog.de/podcast/experts-talk/chat-with-alberto-ferrari/). In it he says, "What scares me is the amount of people that are building reports that are wrong, that are producing wrong numbers but don't know that. They just trust the numbers they see because they have no way of controlling them or debugging them. Maybe they don't have the knowledge to correct them." So in other words, we must never just believe the numbers that DAX gives… Read More

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