Calculated Columns vs Measures

• Calculated Columns:

  • Definition: A calculated column is a new column added to a table that performs row-level calculations. The values in the calculated column are computed for each row when the data is loaded or refreshed.
  • Use Case: You might create a calculated column to derive a category from existing columns, such as creating an age category based on a person’s birthdate.
  • Example: Age Category = IF(Year(TODAY()) - Year('Person'[Birthdate]) > 30, "Above 30", "Below 30")
  • Performance: Calculated columns are stored in memory, which could lead to performance issues if you have a large dataset.

• Measures:

  • Definition: A measure is a calculation used to aggregate data, typically based on user-selected context (e.g., filtered data or selected columns). Measures are dynamic and calculated at query time.
  • Use Case: Measures are used for aggregating data, such as calculating sums, averages, or percentages.
  • Example: Total Sales = SUM('Sales'[SalesAmount])
  • Performance: Measures are calculated dynamically and do not consume memory the same way as calculated columns, which makes them more efficient for large datasets.

Creating Custom Columns Using DAX

• DAX (Data Analysis Expressions): DAX is the formula language used to create calculated columns and measures in Power BI. It allows for complex data manipulations and aggregations.
• Creating Custom Columns: You can use DAX to create custom columns that perform calculations based on existing columns in your data model.
  • Example: A custom column that calculates the profit margin: