Archive - Blog Posts on DAX

My Supercharge Excel and Supercharge Power BI Books Available on Amazon - After what seems like a lifetime of waiting for the paperback book economy to tick its required boxes, both of my new books Supercharge Excel and Supercharge Power BI are now available to ship to most countries in the world from Amazon.com. This is a good opportunity for me to clarify how these new books […] Read More
Time Intelligence Using DAX - Level: Intermediate Last week I presented a webinar with Chuck Sterling (from Microsoft) on the topic of Time Intelligence.  This was a YouTube broadcast event that anyone could join live, however it has also been recorded and can be watched at any time (see below). This article is a follow up from that webinar with […] Read More
Introduction to DirectQuery - Level: Intermediate to Advanced Matt here.  I recently met Parker Stevens online through LinkedIn – he too is a Power BI fan, just like me.  Parker has shared a few techniques with me that I found very interesting.  These topics have not been covered on my blog before and hence I thought it was a […] Read More
Filtering a Column via a Visual AND CALCULATE - Level: Intermediate. Sometimes, just when you think you are getting the hang of the DAX language, something happens that completely baffles you.  Such events can cause you to doubt your own learning and progress. But let me assure you – this happens to EVERYONE at sometime or another.  In this article I am going to […] Read More
Show Me All Invoices Containing this Product - Level: Advanced I was answering an interesting forum question last week, and as is often the case I thought it would make an interesting article to share with you all here.  I have modified the scenario a bit for this article and have built out some test measures to help explain some concepts. Problem Definition […] Read More
Compound Growth using DAX - There are some things that are very easy to do in Excel that don’t seem to have an immediately obvious and simple solution in DAX.  One such problem is when you want to apply compound growth on an investment.  Let me make up an example so it is clear what I am talking about. Simple […] Read More
Items NOT Selected in a Slicer? - I recently attended a school reunion (class of 1981 for what it’s worth – wow, I’m old!).  We all had a great time catching up, looking at old photos, taking new photos etc – how things have changed!  Anyways that got me thinking that it would be great to do something cool with Power BI […] Read More
EARLIER vs EARLIEST in DAX - Level: Advanced The EARLIER() and EARLIEST() functions are 2 of the harder functions in DAX to get your head around. If you come from a programming background it will probably be easy for you.  However as I have repeatedly stated on my blog, one of the many great things about Power BI is that it […] Read More
Sales of Same Products to Other Customers - Level: Intermediate I was helping someone on a forum recently and I thought the question and solution would make an interesting blog article. I have adapted the scenario to work with Adventure Works customer purchases, but this concept would work equally well across other entities, such as sales territories, calendar years etc. Problem Definition The […] Read More
Virtual Filters Using TREATAS - Level: Intermediate I have been aware of the TREATAS function for a very long time.  I have tried to understand how it works a few times but could never really work it out. I think part of the problem is the function name TREATAS is not very descriptive.  To contrast this point, the FILTER function […] Read More
Line Chart Conditional Formatting - I’m really pleased and excited to introduce you to Daniil Maslyuk (Twitter: @DMaslyuk).  I first met Daniil about a year ago (originally via email) as he was preparing to move to Australia.  Daniil made the plunge shortly there after and migrated to Australia (from Russia) – he now works as a Power BI consultant for Agile BI here in […] Read More
Killer Tips to Write Harder Measures - Level: Intermediate Background I was reading the questions at http://community.powerbi.com today and saw an interesting question.  In short the OP wanted to know if there was a way to identify (via a measure) if the row in a table had a colour =”Blue”, but also to check that there were no other rows with the […] Read More
DAX UNICHAR Function in Power BI - I first learnt about the new DAX UNICHAR function in Power BI by reading Chris Webb’s blog (which was also the inspiration for this article today).  One of the uses of the UNICHAR function is to be able to include non text characters inside visuals.  When you combine a logical set of these codes (icon sets) you […] Read More
Using Variables in DAX - Level: Intermediate The variable feature in DAX has been around for more than a year and I have simply never got around to writing an article about it – I am changing that today.  Let me start off with some version information and syntax and then I will show you are real life example where I […] Read More
How To Solve a Complex DAX Problem - Level: Advanced I was helping someone on Power Pivot Forum last week with a tricky calculated column question.  As an aside, you may know that I always advise beginners to keep away from calculated columns.  You mostly don’t need calculated columns because in most circumstances a measure is a better option to use (read more […] Read More
New Functions – GENERATESERIES, SELECTEDVALUE - Level: Beginners/Intermediate In the August 2017 update of Power BI Desktop Microsoft released the new What If feature (first demonstrated at MDIS in July).  This new What If capability uses two new DAX functions to complete a task that you have always been able to do manually.  You still can of course still do it […] Read More
When to Use SUM() vs SUMX() - Level: Beginners/Intermediate It has been 2 years since I first wrote an article about SUM() vs SUMX().  I decided to write a new article to refresh this topic and add in the things I have learnt over the last 2 years. SUM() vs SUMX() First let me start with an overview of both of these […] Read More
The ALL() Function in DAX - Level: Intermediate In this article I am going to write about the ALL() function, show how it works and explain a common use case.  I will also cover the unexpected behaviour that occurs when using the ALL() function in Power BI Desktop – more on that later. For this article I will use my simplified copy […] Read More
My MDIS Presentations - Level: Beginners/Intermediate As those that read my blog regularly would know, I have been at the Microsoft Data Insights Summit in Seattle Washington for the last 2 days. I had the privilege and pleasure of presenting two sessions at the event. For those that were not able to attend, here are the videos of my […] Read More
Quick Measures and Query by Example - 2 fabulous new updates again in the April update of Power BI Desktop.  Here is my quick review so you can see how great these things are. Quick Measures Quick measures are designed to help you get started with DAX, without knowing any DAX!  OMG I will be out of a job!! Once again this […] Read More