Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

M for Calculated Column in Direct Query Mode

Hello Power BI Gurus,    I have a bit of a issue that I am hoping to get some help on.    Issue: I am creating a report from our on prem SQL Server (using the enterprise data gateway) in Direct ...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Anonymous ,

     

    Is this your desired result?

    NewColumn =
IF (
    Forecasts[ForecastID]
        = CALCULATE (
            MAX ( Forecasts[ForecastID] ),
            ALLEXCEPT ( Forecasts, Forecasts[FiscalYear], Forecasts[FiscalMonth] )
        ),
    TRUE (),
    FALSE ()
)

     

    By the way, it is not possible to add custom column with M code in Direct Query Mode, so, you should use DAX instead.

     

    Regards,

    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

Hi Anonymous ,

 

Is this your desired result?

NewColumn =
IF (
    Forecasts[ForecastID]
        = CALCULATE (
            MAX ( Forecasts[ForecastID] ),
            ALLEXCEPT ( Forecasts, Forecasts[FiscalYear], Forecasts[FiscalMonth] )
        ),
    TRUE (),
    FALSE ()
)

 

By the way, it is not possible to add custom column with M code in Direct Query Mode, so, you should use DAX instead.

 

Regards,

Yuliana Gu

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Thanks v-yulgu-msft that worked. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi @v-yulgu-msft

      I used this but got an error 

      Function 'CALCULATE' is not allowed as part of calculated column DAX expressions on DirectQuery models

       

      The functions CALCULATE and ALLEXCEPT are also underlined - giving me a message 'ALLEXCEPT' is not a function

       

      Is there another way to do this in DirectQuery mode?