Category: Formula Language

Power BI: Measures Vs Calculated Column

In earlier blogs, we have understood various jargons in Power BI. To add to these jargons further,  there is concept called Measures in Power BI. Generally this term gets confused with calculated columns.

Let us see what Measures is all about and if it is really an alternative to Calculated columns. Actually its not!! 

We have seen how to add calculated column in earlier blogs such as Win_by_Runs. Here we wanted to know the number of runs by which the match was won, and it was for every match. Hence the calculation was for row wise and result is shown row wise. 

In short, Calculated columns use the context of the row and provide calculation row wise. Hence whenever we need any such calculation at row level, we have to use the calculated column. 

On the other side, if we need any calculation as aggregation of the entire data or based on any filter, we have to use Measures. In our IPL data, if we want to see total runs for all matches, we need this as aggregation of values of one column and all rows. This can be achieved by Measures. 

Let us go back to our IPL example again to see how it works. If we want to know total runs scored. Below are the steps to create the Measure. 

Navigate to Modelling tab, Select the table in which you want to add the measure and click on New Measure as shown in below snippet.

Measure1

By default the Measure 1 will be the name for this Measure, depending on how many measures we have already added in this. Here I already have few measures created, hence its showing Measure 3 as shown in below snippet.

Measure2

Let us change the name and add the formula as shown in below snippet. 

Measure3

Here we have to understand that, unlike the columns, Measure will not be seen as a column in the table, and will be available only as formula to be calculated at runtime.

Hence we won’t be able to see the result of this formula now.

Measure4

If we want to see the output of this measure, we need to add this to Report and can see the value. Either we can directly drag this measure to report and see the bar chart by default or add a card visual and then select the measure as field. This will calculate the value of the measure and display on the visual.

Measure5

We can also add filters to the measures. If we want to see the total runs scored in Mumbai location, we can write use the formula as Calculate and add filter using match formula as shown in below snippet.Measure7

If we see the output of this filtered measure, we can see total runs scored in Mumbai as shown in below snippet.

Measure6

Summing up the Measures vs Calculated Columns comparison considering three parameters

  • Memory utilization:
    • Calculated column is stored in the in memory of the database and it will consume the memory even if nobody is querying this column. However while user interacts with reports, there is no impact on user’s virtual memory utilization
    • Measures is always calculated at run time, it actually impacts the virtual memory, however it saves in memory of the database.  Hence one should be careful while adding measures in the Power BI
  • Evaluation Context: 
    • Calculated column to be used when calculation is in row context 
    • Measures to be used when aggregation is expected from multiple rows
  • Visibility
    • Calculated columns’ output is visible in the table as soon as they are created 
    • Measures’ output is not visible until we add it to the report in terms of visual

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like / share button if you like my article.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI: Measures Vs Calculated Column” →

Power BI Jargons: DAX/M/Power Query

In earlier blogs, we have seen how to analyse data in Power BI with the help of data modeling and visualization techniques. Let us go further in depth of data modeling and how we can tweak the data with the help of different ways which are available in Power BI.

When we hear about Power BI, we hear different jargons like Power Query, DAX , Formula language, M etc. Let us see one by one what these jargons mean and how helpful and important these are while analyzing the data in Power BI.

  • Power Query: is the data extraction and transformation engine. The engine comes with a formula language and a graphical tool. Power Query can connect to various data sources. The graphical interface of Power Query is less powerful than the actual scripting language used behind which is formula language
  • M: is the scripting language behind the Power Query which is actually Power Query Formula language. However since this name is quite lengthy, even Microsoft prefers to call it as M. It is used for data transformation and to be used before you load the data for analysis.
  • DAX: is the Data Analysis eXpression language. It is made for analytical data calculations. Once the data is loaded, DAX can be used to analyse the data in depth. Someone who is knows Excel formulas, would find this familiar.

Both M and DAX serve different purposes such as M is used only in query editor and DAX is used in data modelling. Yes! The same data modelling techniques which we have seen in my earlier posts.

Let us see one example how we can add custom column using both the languages.

Referring to our previous data source of IPL, if we want to add column displaying the runs by which match was won. Let us add this column first using the query editor. As explained in above definition, this would be done before the data is loaded in the Power BI. In Power BI desktop, let us click on Edit query button as shown in below snippet.

Then click on the Add Column Section

There we can see custom column button, which actually asks for the definition of the custom column. Kindly refer to below snippet.

We can give desired name to the column and then write the definition of the column. Here we are adding a column to show by how many runs the match was won, we have to check if win_type is runs, show the win_margin. Referring to below snippet, we can give a name to the column and write the formula. As we type the formula and press ok , it would show if there are any errors. 

 Once all syntax errors are removed, it wil give the message that no syntax errors have been detected, as shown at the bottom of below snippet.

I found the scripting of M similar to old VB code. This way we have created a new column before the data gets loaded in Power BI. 

Once the column is created, lets apply the changes and close the query editor.

Below would be definition of the new column created 

If we look at the advance query editor, our data query would look like as shown in below snippet.Power Query15

As M is step by step language, we can see the “added column” step in the right hand section of the query settings.

Power Query16

Now let us see how to create similar column using DAX. Let us go to Modeling tab and  click on New column as shown in below snippet.

Let us add the definition of the new column and new column will be created. This is very similar to Excel formulas

Now we have same calculated columns created using M -Power Query and DAXDAxandPQ

In this blog , we have seen how we can use Power Query- formula language and DAX to achieve the same results . We can always remember that Power query is the ETL langauge and is step by step language, where as DAX is your analysis language. Now the million dollar question is when to use what?

You can refer below blog to understand the  difference between these two. Hats of to the writer, really liked the way it is explained.

http://www.sqlgene.com/2018/04/03/m-vs-dax-chopping-broccoli-vs-planning-a-menu/

Hope you enjoyed learning these jargons!

Thank you for reading my blog, if you have any questions please mention in the comments and hit the like / share button if you like my article.

Keep Learning !! Happy analyzing and be Powerful 🙂

Continue reading “Power BI Jargons: DAX/M/Power Query” →