Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
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
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
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?
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |