DAX

Building from my last post, Performance Metrics: Average, Mean, Median, and Outliers, where I covered the topic of when to use Median instead of Average, in this post I’m going (to try) to sh…

When analyzing data, I often find myself computing the percentile value. As I am a big fan of PowerPivot , I went w...

Please note the performance is not optimized for a very large dataset, try with your own data. Create a Median Meausre [Lowerbound] =MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold] = COUNTROWS( BoxOffice ) / 2 ), BoxOffice[TicketsSold]) [Upperbound]=MINX( FILTER( VALUES( BoxOffice[TicketsSold]), CALCULATE( COUNTROWS( BoxOffice ), BoxOffice[TicketsSold]

Did you know that it’s easy to transform Power BI’s Line Chart into a Slopegraph? No DAX or fancy workarounds are required. Slopegraphs are a great way to judge the rate and magnitude o…

How to Master SUMX in DAX. Master SUMX using these simple techniques shown using examples. This makes DAX iterators easy! NFORM +61 8 7228 0515

This article presents different techniques to compute a rownumber column in DAX based on a specific ranking, comparing slow and optimized approaches. In se

I read a quote somewhere along the lines of… If you are trying to solve a problem with regular expressions… you now have two problems This seems a little harsh for a useful tool that ha…

Welcome to a new series that I’m starting with a few colleagues at Pragmatic Works. The goal of this series is to document many design practices and solutions to problems that we often run into wit…

Just a quick one today on how you can use the Power BI quick calcs with you own date table. I have a very simple table with a date column in it: Now I want to show the YTD over time (so crossing mu…

So for this example it is best explained with an image below, then some additional context afterwards If you look above we have got the [Sum of Sales] for Dec 2010-11 NOTE: This I selected in the S…

This article shows how to define variables in DAX queries and introduces the differences in scope when there are multiple EVALUATE statements. If you read

As you probably know, charts (and lots of other visualisations) in Power BI have titles that can be set to any piece of static text. You can do this by selecting the chart, going to the Format tab …

With the new Power BI desktop release (July 2017) comes a new DAX function called “SelectedValue”, this DAX function is a convenience feature to help you with working with selections. T…

This article describes how the SELECTEDVALUE DAX function simplifies the syntax required in many scenarios where you need to read a single value selected in

If you want to reach into a difference table in DAX you will need to use either the RELATED or the RELATEDTABLE expressions.

Varface – Say hello to my little friend I used to use the bad joke, “CALCULATE is the Ummmm of DAX” – like, when you want to start talking/writing out a DAX expression before you really know the answer, you just start typing CALCULATE slowly cuz you know it’s coming – it always does!But now, …

PowerPivot's EARLIER function is a powerful way to calculate values based on rows that similiar to the row currently being evaluated.

A challenge to which I infrequently return is attempting to create various “new” chart types using only Power BI’s native visuals. For example, I have tried to replicate a Horizon…

I cannot tell a lie.  Nah… that’s a lie.  I just lied.  But I am speaking truth here… I’m excited to write this post today!  I get the rare joy of sharing something cool with you… while doing my favorite thing… complaining about stuff! We are going to talk about the KEEPFILTERS function. Before I …

Context Transition in DAX is when you transform the row context into the filter context.  In order to understand Context Transition a practical knowledge of Row and Filter context is needed, along with a practical knowledge of CALCULATE. Understanding Context Transition is not too complicated, however it is something that DAX users get caught out […]

Powerful and all that CALCUATE is, there is a limitation to the filters that it can apply. In this article we will look at one of these limitations

Would it not be nice if your Title changed based on what you selected on your slicer? In this example below I am going to show you how I used a Country slicer to dynamically change the title of my …

I recently wrote a few articles about a new function in DAX and the use of variables. For those of you using Power BI or Azure Analysis Services, you always

This article explains how to improve DAX queries using GENERATE and ROW instead of ADDCOLUMNS when you create table expressions. A very popular DAX functio