All courses available in-class or via remote login

Computer screen with graph

Importance of DAX in Power BI

March 29, 2018

If you are not a consistent user of Microsoft Power BI you are likely unfamiliar with the term “DAX”, however that doesn’t mean that you haven’t been using it! Data Analysis Expressions or DAX is the library providing users with an array of methods to analyse and shape data, and is a core component of SQL Server Analysis Services, Power Pivot in Excel and Power BI Desktop. A good understanding of DAX can make report authoring more simple and more powerful. However, without a functional language background it can often be overwhelming for beginners and even some seasoned veterans to understand the importance of DAX in Power BI.

 

So what is DAX?

First of all, DAX is mostly used to populate a new column in a table based on numbers, texts and related data. Also, like Excel (filing formulas up/down/right/left or table formulas) you write a DAX expression once for the column and it is evaluated for each row. Each time the data is refreshed the calculation is performed. Also DAX is commonly used in measures, a value (often numeric) that is used in a visual or table of your report.

The value is calculated every time the visual is refresh and the expression applies to that cell or element of the visual. So, one measure may be calculated many times within the one visual, under different contexts. Therefore, a valuable capability of DAX is the ability to modify that context to view data in a different way. A simple example of a measure is to calculate an average or total, and can be given a specific context such as a product colour, month of the year or any other attribute in the data model.

 

What’s so important about DAX?

Because it’s so simple to create a new Power BI Desktop file and import some data into it. It is also relatively simple to create reports without using any DAX formulas at all. But, the difficulty comes when you need to analyze data and related data elements in the model (such as growth percentage across product categories rankings and statistical outcomes over different date ranges). The more capable Business Intelligence creator, it is inevitable that you’ll need to learn and use DAX regularly. DAX formulas provide this capability and many other important capabilities as well. Learning how to create effective DAX formulas will help you extract the most useful information from your data. When you get the information you need, you can begin to solve real business problems that affect everything from your bottom line to the happiness of your clients.

 

Important things to know about DAX

1.Don’t worry about learning all functions in the beginning



DAX has many possible functions, and it can be hard to know where to start. Focus on the basic functions first:

SUM, AVERAGE, MIN, MAX

COUNT, COUNTROWS

CALCULATE

FILTER

IF


2.Learn the concepts

 

Instead of spending your time rote learning functions spend your time learning the core concepts and patterns of the language. This is the fastest and best way to learning DAX. Build a library of important DAX information and work in iterations. DAX may look like Excel functions but the underlying theory is very, very different. These fundamentals of the language must be understand early in the learning process.

 

 
3.Keep your functions as readable as possible



The nature of DAX functions means having a lot of calculations happening on a single line. Therefore, keeping your code legible is a priority. Delete unnecessary elements, use descriptive names, and be explicit with your references.

 

Object Names, Columns and Measures

  • If you don’t need it, delete it
  • Keep internal column names hidden
  • Rename columns, use calculated columns & measures to define user-friendly names
  • Use explicit measures were possible

 



4.Sort your measures by category



Have a mental template for the different types of calculated measures that you’ll encounter during the code process. Instead of starting over from scratch each time, you’ll already be halfway there with a rough outline for your function.

 

Define Measures in Categories:

  • Simple aggregates
  • Time variances
  • Ratios & differentials
  • Business – specific calculations

 

Learning DAX in Power BI can be confusing and challenging for new users and veterans alike. Hopefully after reading this article the language of DAX is a little less frightening.

Contact Us

What is Remote Training?

Valued by Individuals

4.8/5
Over 23062 Reviews
great information, good coarse -Project Advanced Perth
Damian - Project Advanced, .
well worth doing very helpful -Project Advanced Perth
Marc - Project Advanced, .
Very clear instruction from Jenny and she was very helpful. A very enjoyable intro course to Excel and I learned many tips which will be extremely helpful at work. -Excel Beginner Perth
Dani - Excel Beginner, .
I loved the course! It was really helpful and Jenny was a great teacher :) -Excel Beginner Perth
GRACE - Excel Beginner, .
I really enjoyed this course! very useful :) -Excel Beginner Perth
Renee - Excel Beginner, .
Very informative session on introducing Power BI to people that have never used Power BI. Steven presented it in a very clear and concise manner, at a slow and gentle pace for the group to understand. Steve was able to address most of the questions that the team ask him, and for questions he do not know, he looks into and come back with answers. It shows his tenacity and dedication in educating the team. He regularly makes sure the team is on track before proceeding, making sure that people are not lost or confused. Thanks -Power BI Beginner Remote Online
Jack - Power BI Beginner, .
Good Session, Ken is very Knowledgeable and fast paced, but unable to assist anyone who falls behind. -Excel Intermediate Melbourne
Darren - Excel Intermediate, .
It was good course but I have problems with my technical so ,......I will do it again face to face. thanks Kevin -Excel Intermediate Melbourne
phuong - Excel Intermediate, .
The pace of the training was great - not too fast or slow. John is very polite and patient. -Power Point Level 1 & 2 Remote Online
Bunnary - Power Point Level 1 & 2, .
Terrific instructor and very nicely structured course -Power BI Beginner Remote Online
Henry - Power BI Beginner, .
Very helpful course. I learnt a lot of fundamental skills which will assist productivity in both PowerPoint and Office more broadly. -Power Point Level 1 & 2 Remote Online
Sean - Power Point Level 1 & 2, .
Very insight as a basic starting course, could be a little too quick if the class is not as savvy in similar software -Power BI Beginner Remote Online
Vincent - Power BI Beginner, .
informative course. Feel like i've got a good grasp of the basics. Looking forward to trying to build a few reports with our existing data. Bring on the intermediate course! Thanks. -Power BI Beginner Remote Online
Brendan - Power BI Beginner, .
Great training, very informative and the day was not boring at all! Very informative. My work will improve out of sight from this. -Excel Intermediate Melbourne
Emily - Excel Intermediate, .
Great course -Power BI Beginner Remote Online
Nathan - Power BI Beginner, .
Very informative and interesting, really enjoyed the class. Really worth the investment. Thank you Jason -Analysis & Dashboards Brisbane
Cheryl - Analysis & Dashboards, .
I think this was a good training. it was a little slow at times but this is probably because I knew more about powerpoint than I thought I did. I think it would be beneficial for me to do a more advanced training. I did pick up some good tips though. -Power Point Level 1 & 2 Remote Online
Laura - Power Point Level 1 & 2, .
Learnt some helpful tricks and tips to make processes more efficient/faster for me, as well as just general navigating/understanding different parts of Powerpoint we can use. -Power Point Level 1 & 2 Remote Online
Veronica - Power Point Level 1 & 2, .
Learnt quite a few new things which will assist in my day to day work Informative and helpful -Analysis & Dashboards Brisbane
Catherine - Analysis & Dashboards, .
Ken was wonderful at explaining everything -Excel Intermediate Melbourne
Lauren - Excel Intermediate, .

Trusted by Business

Procured by Government

Map of Australia