Blog

Calculated Column and Measures in Power BI using DAX

You are currently viewing Calculated Column and Measures in Power BI using DAX

DAX is formula language of Power BI and other Microsoft Power Pivot tools but why is DAX an integral part of every Power BI training course?

Here’s why you should invest your time in learning DAX:

  • The better you learn DAX, the smatter you can make your Dashboard.
  • DAX is more than Power BI. It gives you an additional skill to work in Microsoft tabular products such as Microsoft Analysis service, Microsoft Power Pivot for Excel.
  • It makes you a better data professional.

DAX surely does involve a lot of data manipulation to bring about the hidden information in raw data. There are two places where we can write DAX:

  • Calculated Columns.
  • Calculated Measures.

Calculated columns and measures are defined to add missing information in a data set. They add value to the data set by bridging the gap between data model and data visualization.

Calculated Columns

Calculated Columns are a method for adding additional columns to the tables. Calculated Column is evaluated for each row of the table, creating a row context for each row. A naked column can be used while creating calculated columns. A Calculated Column is typically used in the Axis, Legend, Filter or Group area of a visualization.

Calculated Column is more intuitive for excel/business users. However, calculated Column takes more disk space as its stored in Data Set. Therefore, creating a calculated column must be avoided if its use is un-certain.

To Create Calculated Columns:

1.Click on Tables in the Fields.

2.Click on New Column

Calculated Measures

Calculated Measures are used to perform aggregations. They do not add a value for every row in the table. This makes measures memory efficient as they do not appear in your data set. They are lightweight alternatives to Calculated Columns. You cannot however use naked columns while writing regular DAX expressions for creating a measure.

Measure is used in the values area of a visualisation. Therefore, they are only created when a measure is used in a visual.

To Create Calculated Measure:

  1. Click on Tables in the Fields.
  2. Click on New Measure

When to use calculated column and/or measures

  • Calculated columns are only preferred over Calculated measure when you need column values in a slicer.
  • Calculated column preferred when a slicer is needed.
  • Calculated measures are memory efficient and preferred over calculated columns unless the latter is necessary.

Best Practices in DAX

Lets look at the best practices that you must follow when writing the DAX expressions:

  • Use fully qualified table names when referring to a column – TableA[ColumnX]
  • Use just the measure name when referring to a measure – [Total Amount]
  • Write once and re-use measures
  • It is best practice for Power BI users to remove the dim and fact prefixes from the table names before importing these tables into Power BI. These prefixes have meaning to IT folk and help identify the type of table, but given that these table names will be visible to business users who use your Power BI reports, it is best to remove the prefixes after import by simply right-clicking a table and renaming it.
  • It is best practice to always load a numeric column in your lookup table for every alphabetic column that needs to be sorted in a different order. You should therefore always include a numeric column in your Calendar table for days of week as well as months of year.

Tips for Getting Started in DAX:

If you’re a beginner in DAX start by:

  • Writing DAX expressions in steps.
  • Start with simpler expressions.
  • erUse a matrix visual to visualize values for a calculated column and measure before you can use measures freely.
  • Format your DAX expressions to make it readable.

Conclusion:

DAX is a formula language for Power BI. It is used to enhance the data analytics capabilities of Power BI. DAX expressions are entered in formula bar and can be used to either write calculated columns or calculated measures. This blog covers the concept of calculated column and calculated measures.

Are you a data analyst and want to learn more about Power BI?

Why not sign up for Power BI training in Australia.  We provide our services in the following regions:

 

 

 

Leave a Reply