Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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! 

1 ACCEPTED SOLUTION
v-yulgu-msft
Employee
Employee

Hi @Anonymous ,

 

Is this your desired result?

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

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @Anonymous ,

 

Is this your desired result?

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

1.PNG

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Thanks @v-yulgu-msft that worked. 

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.