• Samuel Woldu

Being Lazy With DAX - Measure Branching

Updated: Feb 12, 2019

Who doesn't like being lazy aka efficient when it comes to any task in life? People in life, especially in the IT industry always find a way to automate parts of their job. If there's any routine task that takes me more than 2 hours a week to complete, I'll try to find a way to automate or at least stream line it. This could be using Excel macros, SQL stored procedures/agent jobs, Microsoft Flows etc.

Anyone that's using Power BI whether they're a rookie or a rock star, have had to go through the daunting task of creating loads of DAX measures. You create one measure and then practically slice and dice that same measure 101 different ways. In part 1 of being lazy with DAX, we'll cover the basic DAX measure branching technique.

I strongly recommend using this technique when you create all your measures otherwise you could end up creating yourself even more work later down the line.

What is measure branching? Lets say you have a fact table that contains sales for your organisation and you need to create a measure that sums up the sales. In this example the table is named 'Fact Sales' and the column for sales is named [Selling]:

Lets say you now need to create a measure for sales last year.

We might as well add in sales for the previous month while we're at it.

You're now in the position to do basic visualisations, assuming you have correctly set up your data model. You select a bar graph to represent sales compared to last year where you can now even drill down to the day, you're starting to get somewhere with the report.

Then you receive an email and find out that the correct figure only needs to include sales where the [Department Group] column in your fact table = 1.

Let's assume you've created a further 10-20 different DAX measures similar to the above where you have defined the sales using the SUM([FactSales[Selling]]) for each measure .

You would now have to go through each measure and add in the necessary filter for the correct department group.

For example the Sum Sales measure would need to be amended to the following:

You would need to go through each of your 10-20 measures and add in a filter for sales where the [Department Group] = 1. I've seen some models where there's 100+ measures so this could easily rack up hours of work.

Ok, take 2... ignore all the DAX above for now... stay with me! We're going to measure branch.

Step 1 -

We'll create the first measure so it includes the filter where [Department Group] = 1.

Instead of adding in the [Department Group] = 1 filter into the Sales LY measure,

we can refer to the [Sum Sales] measure above and the [Department Group] = 1 will feed though to the Sales LY measure automatically. Any future changes will also feed through. Example below:

If you have set up all your sales related measures to point at the initial [Sum Sales]

measure which already includes the filter for the Department Group, then any future changes to the base measure will feed through automatically.

I will be posting Being Lazy With DAX (Part 2) - Variables in the next couple days.

If you have any questions please comment down below:) Thanks for the read, peace!

87 views0 comments