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 Query mode. I need to create a calculated column that will check if a record is the latest record given a few conditions:

 

Logic:
IF the forecastID is the greatest value for the fiscal month value AND current region value, then true, else false.  

 

Background on the data:

These are sales forecasts that are turned in by the sales team. Each month they are expected to return 3 records, an oringal forecast, a mid-month forecast, and then a final forecast. I need to keep all of the revisions in new records, hence the ForecastRevision field in the data. The forecastID is the primary key of the table and is set to auto-increment, so every time they submit a new sales forecast record a new forecastID will be generated that is greater than the previous one.

 

I have linked a .pbix file with some anonymized data here: DropBox. Obviously this isn't going to be with a direct query connection, but any help would be much appreciated!

 

Thanks! 

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

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft 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
        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?