Forum Discussion

DoctorYSG's avatar
DoctorYSG
Helper III
1 year ago
Solved

DAX DEFINE COLUMN

Can someone explain to me why one can define calculated columns in DAX studio, but not in the PowerBI DAX query tab? (It does not allow that syntax, only DEFINE MEASURE. See: DAX Queries - DAX | M...
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Hi DoctorYSG 

    DEFINE COLUMN in DAX query view appears to work for me, but there an issue with Intellisense.

    For example, this test query worked as expected for me:

    DEFINE
	TABLE MyTable = {1,2,3}
	COLUMN MyTable[Value Squared] = MyTable[Value]^2
EVALUATE
    MyTable

    If you run Performance analyzer on a visual containing a visual calculation then select Run in DAX query view, the query should also contain DEFINE COLUMN.

Nasif_Azam's avatar
Nasif_Azam
Super User
1 year ago

Hey DoctorYSG ,

In Power BI, DAX behaves differently depending on where and how it is executed. The key to understanding this discrepancy lies in the difference between the DAX query context (used in DAX Studio or Tabular Editor) and the data model context (used inside Power BI Desktop). Here's a detailed breakdown:

 

1. Why can you define calculated columns in DAX Studio but not in Power BI's DAX query tab?
DAX Studio Supports DEFINE COLUMN Because:

  • DAX Studio is a client for executing DAX queries directly against the Analysis Services engine behind Power BI or SSAS.
  • When you run a DEFINE COLUMN in DAX Studio, it:
    • Defines a temporary column not stored in the data model.
    • Exists only in memory for the duration of that query.
    • Is useful for what-if analysis, debugging, or testing expressions.

Example:

EVALUATE
VAR ProductWithMargin = 
    ADDCOLUMNS(
        Products,
        "Margin", [SalesAmount] - [CostAmount]
    )
RETURN ProductWithMargin

Or using DEFINE COLUMN explicitly:

DEFINE
    COLUMN Products[Margin] = Products[SalesAmount] - Products[CostAmount]
EVALUATE
    SELECTCOLUMNS(Products, "Product", Products[ProductName], "Margin", Products[Margin])

 

2. Power BI DAX Query Tab Does Not Support DEFINE COLUMN Because:

  • Power BI's DAX query tab (such as in Performance Analyzer or new DAX query view) is primarily designed for measures and queries, not for modifying the data model.
  • DEFINE COLUMN is not supported because:
    • It implies creating a calculated column, which in Power BI is a model-level object.
    • All model-level calculated columns must be defined in the modeling tab, not in a query.
  • Power BI expects DEFINE to be used only with:
    • DEFINE MEASURE
    • DEFINE VAR

Example:

DEFINE
    MEASURE Sales[Total Sales] = SUM(Sales[Amount])
EVALUATE
    SUMMARIZECOLUMNS(Customer[Name], "Total", [Total Sales])

Summary:

 

 

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.


Best Regards,
Nasif Azam