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 | Microsoft Learn


  • 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.

5 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    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.

    • DoctorYSG's avatar
      DoctorYSG
      Helper III
      1 year ago

      Confirmed. It is an intellisense bug, not a DAX limitation of the Query Tab. Any chance we can submit a bug and get it this fixed?

    • DoctorYSG's avatar
      DoctorYSG
      Helper III
      1 year ago

      OwenAuger 

      I am tryhing to use the DAX query view to Run a query that Defines a COLUMN (and I would like to do that for TABLE as well). I understand that I will get intellisence sytnax flagging, but this one is giving errors about end of input. Any ideas if this is possible?

      ( I really need the VAR for clarity).

      DEFINE
	COLUMN
		'Traces'[zResponseTime] =
		VAR CurrentApp = 'Traces'[AppName]
		VAR CurrentVersion = 'Traces'[AppVersion]
		VAR CurrentBase = 'Traces'[BaseCode]
		VAR CurrentDevice = 'Traces'[DeviceName]
		VAR CurrentUser = 'Traces'[UserName]
		VAR CurrentResponseTime = 'Traces'[ResponseTime]
		VAR CurrentActivity = 'Traces'[ActivityName]

		VAR mean =
		CALCULATE(
			AVERAGE('Traces'[ResponseTime]),
			FILTER(
				'Traces',
				'Traces'[AppName] = CurrentApp &&
				'Traces'[AppVersion] = CurrentVersion &&
				'Traces'[BaseCode] = CurrentBase &&
				'Traces'[DeviceName] = CurrentDevice &&
				'Traces'[UserName] = CurrentUser &&
				'Traces'[ActivityName] = CurrentActivity
			)
		)

		VAR std =
		CALCULATE(
			STDEV.P('Traces'[ResponseTime]),
			FILTER(
				'Traces',
				'Traces'[AppName] = CurrentApp &&
				'Traces'[AppVersion] = CurrentVersion &&
				'Traces'[BaseCode] = CurrentBase &&
				'Traces'[DeviceName] = CurrentDevice &&
				'Traces'[UserName] = CurrentUser &&
				'Traces'[ActivityName] = CurrentActivity
			)
		)

		RETURN
			DIVIDE(
				CurrentResponseTime - mean,
				std,
				0
			)



      It would be really nice to be able to testing things out in query view, and get TABLEs and COLUMNS added to the model. (as one can do with measures). I have DAX studio, but I suspect is it read one to the SSAS.

  • 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