Measure Branching and Keeping Calculations Clean

Within most software that does data visualization there is a feature that allows you to also make calculations to help with said data visuals. Within Tableau these will be the calculated fields, in PowerBi this is known as a measure. One important factor to keep in mind especially in PowerBi is something called measure branching. This is where you use previous measures in the next one to expand upon your calculations.  The best way to make sure you’re measure branching properly is to find the core feature of the measure you’re creating and use that as the first part to build upon like the trunk of a tree.

As an example here is a simple KPI indicator that looks at today vs the previous day and shows an arrow depending on growth. This is the below calculation to create that arrow.

if(Field] = 0, UNICHAR(9866),IF(Field >0,UNICHAR(11165),UNICHAR(11167)))

 

 

Now this works for one single KPI, but what if we added extra KPI indicators into the view?

Now we have the same calculation each time for each arrow or dash displayed in the visual. It works for now but imagine if you need to change the style of each one of these indicators. You have three measures that all have a formula that references the UNICHAR Dex three times in each measure. That’s nine times you will have to change the Dex in order to create a uniform look across your visual.

If we now employ the use of measure branching you could create a measure that looks after one arrow each.
EG:

Up Arrow =  UNICHAR(11165)
Down Arrow = UNICHAR(11167)

and place that into the formula.

if(Field] = 0, UNICHAR(9866),IF(Field >0,Up Arrow, Down Arrow)

it means any adjustments to the arrow style will only be required to be done once per arrow now one per arrow per instance.

Measure branching is important to keep the structure of your measure understandable and easy to adjust in case of future changes. Though we have highlighted this in Dax and PowerBi This will be useful across many different software’s to keep your calculations clean!

 

 

If you want to learn more about PowerBi features here are two more posts:

PowerBI 101 – Oliver Inthavong
How to create a date table in PowerBi – Joe Chan

And as always if you have any questions or want more answers feel free to connect on LinkedIn

Stephen Hughes
Author: Stephen Hughes