Posted Feb 23, 2022

In Advanced Analytics, Business Intelligence, Data Science, General

By

Read time 7 mins

Breaking Down the Basics of Power BI

When I was relatively new to the field of business intelligence, PowerBI was my savior – it’s one of the most interactive data visualization tools available on the market, focusing primarily on business intelligence. Power BI lets you easily connect to your data sources, visualize and discover hidden trends in your data, and share those insights with folks across your organization.

However, as I first embarked on my PowerBI journey, I was hit with several unfamiliar – and often baffling – terms that seemingly meant the same thing but were actually quite different.

All the foreign jargon confused me and made this powerful BI tool seem more complicated than I first thought it to be. I felt like I was stuck because I couldn’t understand some of the fundamental concepts of PowerBI, which are essential for someone who is making their way towards using the tool daily. Well, I’m glad that I continued with my journey despite having my fair share of difficulties because, for a long time, I felt like Ryan Howard from Dunder Mifflin paper company (I don’t belong here).

This article is for folks who are relatively new to PowerBI and folks who’d like a quick refresh on some of the fundamentals of the tool. I will try to keep this article as simple as possible and discuss some of the essential topics of PowerBI, including:

  1. Power Query and M
  2. DAX
  3. Calculated Column and Measures

Alright, Let’s get to business!

What is Power Query and M?

Power Query is an engine used to perform data transformation and data preparation operations. The User Interface of Power Query brings in your data from multiple sources and is quite simple to follow. The Power Query Editor within the Power Query Engine is used to apply transformations to your data. The location where data that has been worked on will be stored depends on where the Power Query was used. In simple terms, you are actually performing the ETL (Extract, Transform and Load) operations on your data using Power Query.

The M language is essentially the coding language used by Power Query. I refer to it as the data transformation language of Power Query because any operation you perform on the query is ultimately written in M by PowerBI. The ideal use case where you write code in M yourself is when you have to perform some advanced transformations using the Power Query Engine. All you need to do in this scenario is use the Advanced Editor to access the query script and modify it to your liking or requirements.

How do we decide when to use Power Query or M?

It’s always a good idea to use the Advanced Editor and the M language to fine-tune your functions and data transformation operations or if you find that the existing user interface functions and transformations have limited functioning capacity on your data.

What is Dax?

Data Analysis Expression Language, commonly known as DAX, is used in Microsoft’s Data Analysis products such as Excel, PowerBI, SQL Server Analysis Services, etc. It is a combination of Excel formula, T-SQL, and C#. Since it is an Expression Language, most DAX logic is written after an equal sign (=) as a formula. DAX formulas are pretty helpful in Power BI as they help folks use the data sets they have to their fullest potential. DAX Expressions are usually evaluated from the innermost function to the outermost one at the end.

What exactly is the importance of Dax in PowerBI, and when do I use it?

Let’s say you want to create a visual to analyze a company’s growth percentage of sales across different provinces within one country. The data(fields) you import to PowerBI is usually not enough to bring out what we want in this scenario. You would have to create new measures using DAX and then use the newly created measures to create exclusive visualizations and derive unique insights from the data. Those unique insights can enable you to come up with fitting solutions for your respective business problems that you might otherwise miss if you analyze the data in the usual way. Therefore, it’s safe to say that using DAX in PowerBI for data analysis makes it an innovative and intelligent approach.

Let’s now move on to a topic that had me baffled when I was a newbie in the field of Data Analysis and BI:

What are Calculated Columns and Measures?

Once you bring your data to PowerBI, you can extend it by creating new columns. DAX expressions are used to define the contents of the columns and are evaluated row by row. A Calculated column is very similar to any other column in a table and can be used wherever needed in the report. It can also be used to define a relationship if required. 

The calculated columns are computed when the database is processed and stored in the model. In SQL the columns are computed during query time and do not use memory. On the contrary, all calculated columns occupy space in data models for DAX and are computed during table processing. This behavior would be beneficial whenever we create very complex calculated columns since the time required to compute them will always be the processing time and not the query time, which constitutes for better user experience. If that was complicated, consider that calculated columns use precious RAM.

A measure needs to be defined within the table. This is one of the requirements of the DAX. However, the measure does not really belong to the table. In fact, you can move a measure around from one table to another one without jeopardizing its functionality.

In simple terms, measures and calculated columns both use DAX expressions. The only difference is the context of the evaluation. We can say that a measure is evaluated in the context of the cell that is evaluated in a report or a DAX query. The context of the cell depends on user selections in the report or on the shape of the DAX query. In contrast, a calculated column is computed at the row level within the table it belongs to. 

So how do we decide when to use calculated columns and measures? 

At this point, you might be wondering when to use calculated columns over measures or vice versa. Frequently either is an option, but your use case needs to determine your choice in most situations.

You can use a calculated column whenever you want to perform any of the following operations:

  • Place the calculated results in a slicer, see results in a chart’s axes, or use the result as a filter condition in a DAX query.
  • Define an expression that is strictly confined to the current row. 
  • Categorize text or numbers. For example, a range of values for a measure, a range of ages of students, such as 0–18, 18–25, and so on and so forth.

However, you must define a measure whenever you want to display resulting calculation values that change based on the user selections and view them in the values area of a table or in the plot area of a chart. For example:

  • When you calculate profit percentage on a specific selection of data.
  • When you calculate ratios of a product compared to all products but keep the filter both by year and region.

Let me know what you think about all the topics mentioned above. I would love to hear them; send me an email at [email protected], and let’s talk!

 

About Newcomp Analytics

Newcomp Analytics is a world-class analytics team specialized in consulting and development at the highest level. Wherever you are in your analytics journey, we will help you raise your game in today’s complex business environment. Our team of data scientists, engineers and developers deploy analytics for hundreds of clients across all industries and functions around the globe. We have a simple formula to help you win with data: define the goal, find the gaps, then match you with the right people, processes, and tools to get there – coaching you the entire way. For more information, visit www.newcomp.com or call +1 888 892 4276.

Line graphic of a mountain

No matter where you are in your analytics journey, we'll guide you the rest of the way.

Animated Graphic: mountain-cloud
Consultation Form
First Name
Last Name
What Are You Interested In? *
Animated Graphic: mountain