Category: Power BI

Posted on

Power BI – Use Variables to Improve Your DAX Formulas

DAX can be complicated and challenging. If it is not done right, DAX can be particularly difficult to debug and can slow down the underlying queries. In other words, you do not know if you have the right results. This means the audience of your reports could be sitting and waiting for the output when they filter the report.

Often complex calculations require us writing compound or complex expressions. Compound expressions can involve the use of many nested functions, and possibly the reuse of expression logic and reuse measures multiple times. All of this will add to the time required by the underlaying queries to display the result.

Using variables in your DAX formulas can help you write more complex and efficient calculations. Variables can improve performance and consistency and make the measures easier to read and understand.

In this blog post, you will see demonstrations of some of the benefits, you will achieve by using variables.

In the example you will see the benefits in a year over year profit growth % measure. The math is:

Current year sales minus sales cost minus last year sales minus sales cost divided with last year sales minus sales cost.

To get the values last year you will need to use a time intelligence function.

(You can click the link here to get more insight about time intelligence: https://www.stl-training.co.uk/b/power-bi-mysterious-calculate-function-3-time-intelligence/)

The measure without variables will look like below.

Profit Growth yoy % =

DIVIDE (

( [Sales] – [Sales cost] )

– (

CALCULATE ( [Sales], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

– CALCULATE ( [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

),

CALCULATE ( [Sales], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

– CALCULATE ( [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) ),

0

)

 

And could be visualised on a report page like this in a matrix visual:

 

DAX

Improve performance with DAX

Notice in the DAX above that there are many repetitions. The CALCULATE function is used four times, the PARALLELPRIOD function four times, and the sales and sales cost measure been referenced several times.

This will not only make it complex to read, but will also slow down the query, by asking the query to do the same calculations several times. The measure definition can be made more efficient by using variables.

How to create a variable

You create a variable by typing VAR and then you need to name the variable. After the name of the variable, you need to tell, what you want to store in the variable. To get the result you want to visualise you use the word return.

Below you can see the first variable called profit. This variable will now calculate the profit and store the profit.

 

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

Result

 

 

We have used 4 variables to get the result. 3 variables for the 3 calculations to get to the Result, which here is stored in a variable named Result. The word RETURN is used to explain the measure what to return from the measure. Above the value in the Result variable needed to be visualised.

 

The measure continues to produce the correct result and does so in about half the query time.

Improve readability

In the previous measure definition, notice how the choice of variable name makes the RETURN expression simpler to understand. The expression is short and self-describing.

Simplify debugging

Variables can also help you debug a formula. To test an expression assigned to a variable, you temporarily rewrite the RETURN expression to output the variable.

By changing the DAX RETURN to DiffCurLast

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

DiffCurLast

The matrix will now display the difference value not the in percentage.

And by changing the RETURN to profit you debug the profit = [Sales] – [Sales cost]

Profit Growth yoy % =

VAR profit = [Sales] – [Sales cost]

VAR LastYear =

CALCULATE ( [Sales] – [Sales cost], PARALLELPERIOD ( Dates[Date], -12, MONTH ) )

VAR DiffCurLast = profit – LastYear

VAR Result =

DIVIDE ( DiffCurLast, LastYear, 0 )

RETURN

Profit

 

You can debug all the variables to make sure that each part of the measure returns the right result

Conclusion

Variables in DAX measure can reduce the execution time for the underlaying queries. Make DAX measures easier to read and understand, and much easier to debug.

You can learn about variables on STL’s one day DAX course, if you want to get insight about variables in DAX measures.

Posted on

Exploit Power BI’s Drilling tools to enhance Profitability

In Power BI, you can apply Drilling tools to certain visuals in order to ‘drill down’ through your data. It works by building fields into your visuals that take you from ‘top level’ information (e.g., Sales by Country) right down to specific sales (e.g., Sales by Salesperson) known as ‘granular data’. Drilling down will change the visual to reflect more of the detail. This blog explains the different Drill buttons and their functionality to help you improve the way you analyse your data. You will be able to exploit Power BI’s Drilling tools to enhance profitability in your company.

Why use Drilling tools

Drilling tools really help an end user/stakeholder to fully understand the detail behind a specific visual in order to drive business forward and increase profitability

How it works

Firstly, you need to create a visual containing ‘hierarchical’ fields e.g., ‘Country’, ‘Segment’, ‘Salesperson’ in order to facilitate the drilling tools. In the following example, a line chart is used to show an ‘Order Date’ field by ‘Quantity’ where Power BI desktop will automatically support a Date Hierarchy (see below).

  1. Notice there are four ‘arrow’ buttons above the chart, each with a different function:
  1. Starting at the ‘top level’ i.e., ‘Quantity by Year’ click on 

This action changes the line chart to show ‘Quantity by Quarter’ i.e., the next level down. for If you hover on the data point of Qtr. 2, it highlights the overall Quantity for Qtr. 2 over the entire 8-year period.

  1. Clicking on the    button again brings up another view – i.e., ‘Quantity by Month’:

  1. Again, clicking on the      button again brings up a daily view – i.e., ‘Quantity by Day’:

Note the tooltip showing that the day in which most quantities are sold in any month is the 24th.

  1. The ‘double down’ arrow is now faded out, which means this level is the lowest level of granularity
  2. Click on the    button to go back through the hierarchical levels to the top level
  3. Now click on the ‘Pitchfork’ button   to show the next expanded level:

The tooltip is now showing all Quantities in Qtr. 2 of 2016.

  1. Click on the    button again to show the chart at month level:

Now the tooltip is showing all Quantities in June Qtr. 2 of 2016. Let’s say you wanted to analyse the data further at day level in this particular month of June.

  1. Click on the ‘Drill Down’ arrow   to turn it on showing the following:

This now reveals that the highest performing day in terms of Quantity is the 18th.

Conclusion

So often in business, it is the details that can provide a competitive edge to improve performance. Having access to the various levels of data within a single visual using the Drill buttons will definitely help to achieve this goal. Exploit Power BI’s Drilling tools to enhance profitability in your company.

For course details on our Power BI Reporting course at STL, please click on the link below:

https://www.stl-training.co.uk/syl/355/power-bi-training-courses.html

For more information on Data analysis in business, please click below:

https://www.inc.com/carol-sankar/how-your-data-can-improve-your-customer-relationships.html