Exploring Measures and Calculated columns in Power BI

In Power Bi, both measures and calculated columns are essential for data analysis, but they serve different purposes. Measures are required for dynamic calculations and aggregations, while calculated columns are needed for creating new attributes or categorizing data that won't change based on user interactions. 

Key Points to Note:

Calculated Columns:

Static: They are evaluated during the data refresh process and stored as a column in the model.

Row-Level: They operate on individual rows of data, providing a value for each row.

Stored: They consume space within the data model.

Use cases: Ideal for creating new attributes or categories, performing row level calculations etc.

Measures:

Dynamic: They are evaluated at query time, meaning they change based on the current filter context in a report.

Aggregated: They are typically used for aggregating data across multiple rows (e.g., summing sales, calculating averages).

Not Stored: They are not stored in the data model and only exist as DAX expressions.

Use Cases: Best suited for calculations that need to dynamically adapt to user interactions or filters, like calculating total sales or percentage of total. 

What are Calculated Columns:

Calculated columns are used to add new data columns to the tables based on calculations or transformations of existing data. These calculations can be simple mathematical operations, concatenations of text, or more complex expressions involving conditional logic and functions. Calculated columns are computed during the data refresh process and are stored within the model, making their values static with respect to each row in the table. This means once the calculation is performed and the data is loaded into the model, the values of a calculated column for each row remain constant until the next data refresh.

Adding Calculated Column in Power Bi:

To add a calculated column first we select the table to which we will be adding the column from the Fields pane of the Power BI Desktop window. After selecting the table there will be a new option group on the menu bar.

In this implementation, a new column named “Diabetes_Risk_Status” is created by considering the rows from column “first_tri_fasting_blood_glucose” of “Anthropometry” table. Column value is “True” if “first_tri_fasting_blood_glucose” greater than 92 else “False”.

Below is the DAX expression:

Diabetes_Risk_Status = IF (anthropometry[first_tri_fasting_blood_glucose]>92, TRUE (), FALSE ())

After writing the DAX query press enter to apply it to the data.

Now, we will see a new column named "Diabetes_Risk_Status" in the field pane of the window.

Also, as shown below, a new column named "Diabetes_Risk_Status" appears in the table view.

What are Measures:

A measure is a calculation that can be applied to any visual. We can use all aggregation operations like sum, average, min, and max to create measures using DAX (data analysis expressions). After creating a measure, it will not be saved in the dataset; it is simply a calculation, which will appear in “Fields” with the calculator symbol.

Creating Measure in POWER BI: 

When we create measure, it's added to the Fields list for the table we select. Measures can be used as arguments in other DAX expressions, and we can make them perform complex calculations quickly.

Suppose we want to create a measure to count the patients in a dataset who are at risk of developing diabetes.

Following is the DAX expression:

Diabetes_Risk_Patients = CALCULATE(COUNTROWS('anthropometry'),

anthropometry[first_tri_fasting_blood_glucose]>92)

Here, a measure named “Diabetes_Risk_Patients” is created in the “Anthropometry” table to count the patients with first trimester fasting blood glucose greater than 92.

Choosing between Calculated column and Measure:

The choice between using a calculated column or a measure is extremely important for ensuring that Power BI model works optimally and delivers accurate insights. Measures and calculated columns serve unique roles, so selecting the right one depends on the specific requirements of the data analysis task at hand, including performance considerations, data model complexity etc. Calculated columns appear in the Model view, not Measure.

We usually add a calculated column whenever we want to do the following:

• Place the calculated results in a slicer or see results in rows or columns in a pivot table, or in the axes of a chart, or use the result as a filter condition in a DAX query.

• Define an expression that is strictly bound to the current row. For example, Price * Quantity cannot work on an average or on a sum of the two columns.

• Categorize text or numbers. For example, a range of values for a measure, a range of ages of patients, such as 0–18, 18–25, and so on.

However, we must define a measure whenever we want to display resulting calculation values that reflect user selections and see them in the values area of a pivot table, or in the plot area of a chart – for example:

• To calculate profit percentage on a certain selection of data.

• Measure being evaluated at query time, does not consume memory and disk space. This is more crucial for large datasets.

  
  

Conclusion:

By understanding the strengths of both Measure and Calculated column, we can create more powerful and

insightful dashboards that truly reflect the data's story. Calculated columns are static, while measures are dynamic and allow for more complex calculations based on filters. We can experiment with both to see which suits our data model best. There is no single best approach. The right choice depends on the specific needs of the data model and the type of analysis required.