topic Re: Problems with CALCULATE and ALL when going from Import to DirectQuery in DAX Commands and Tips

Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Tue, 11 Feb 2025 21:04:44 GMT

I'm new to Power Bi and am having an issue when moving from Import to DirectQuery with a calculated column. It ran as expected when using the Import mode, but I can't figure out how to rewrite for DirectQuery.

The DAX is:

Current Record Indicator =

VAR TI = 'Pharmacy Claims'[Transaction ID]

VAR MD =

CALCULATE (

MAX ( 'Pharmacy Claims'[Invoice Date] ),

ALL ( 'Pharmacy Claims' ),

'Pharmacy Claims'[Transaction ID] = TI

)

RETURN

IF ( 'Pharmacy Claims'[Invoice Date] = MD, "Y", "N" )

I'm just trying to get a column with a "Y" if the Invoice Date is the most current otherwise a "N"

Thanks for your help in advance.

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

amarma — Tue, 11 Feb 2025 22:31:36 GMT

hi DaveW31 ,

Can you try the below code in the PowerQuery box when you add a custom column?

let AddCurrentRecordIndicator = Table.AddColumn( #"PreviousStep", // Replace with your previous step name "Current Record Indicator", each let TI = [Transaction ID], MD = List.Max( Table.SelectRows( #"PreviousStep", // Replace with your previous step name each [Transaction ID] = TI )[Invoice Date] ) in if [Invoice Date] = MD then "Y" else "N" ) in AddCurrentRecordIndicator

Hope this helps!

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

Anonymous — Wed, 12 Feb 2025 03:34:51 GMT

Hi DaveW31 ,

There are some limitations when the connection mode of data source is Direct Query, please find the details in the following official documentation.

DirectQuery in Power BI - Power BI | Microsoft Learn

Best Regards

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

shafiz_p — Wed, 12 Feb 2025 05:20:49 GMT

Hi DaveW31 When switching from Import mode to DirectQuery in Power BI, certain DAX functions and calculated columns may not work as expected due to the differences in how data is processed. In DirectQuery mode, calculations are pushed to the underlying data source, which can lead to limitations.

To achieve your goal in DirectQuery mode, you can use a measure instead of a calculated column. Here is how you can rewrite your dax code to create measure:

Current Record Indicator = VAR MD = CALCULATE( MAX('Pharmacy Claims'[Invoice Date]), ALLEXCEPT('Pharmacy Claims', 'Pharmacy Claims'[Transaction ID]) ) RETURN IF(MAX('Pharmacy Claims'[Invoice Date]) = MD, "Y", "N")

This measure will check if the Invoice Date is the most current for each Transaction ID. Add this measure to your report, and it should display "Y" if the Invoice Date is the most current for the Transaction ID, otherwise "N".

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Wed, 12 Feb 2025 22:09:25 GMT

amarma, thank you for your response. I tried your suggestion and it appeared like it would work when I applied it. After 5 hours, I got the message "This step results in a query that is not supported in DIrectQuery mode. Switch all tables to import. The table that this applies to is 70+ million records.

The code I put in Advaced Editor was:

#"Added Custom1" = Table.AddColumn(#"Renamed Columns", "Current Record Indicator", each let ( TI = [Transaction ID], MD = List.Max( Table.SelectRows( #"Removed Other Columns", each [Transaction ID] = TI )[Invoice Date] ) in if [Invoice Date] = MD then "Y" else "N" ) )

#"Added Custom1"

I don't think I messed anything up. Was not showing any issues before I tried to apply it.

Thanks again.

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Wed, 12 Feb 2025 22:10:57 GMT

v-yiruan-msft, I have read that. Still learning how it all works.

Thanks for your help.

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Wed, 12 Feb 2025 22:13:17 GMT

Shahariar Hafiz, I'm trying to add it as a column. Am thinking I can add it into the table in SQL before I load the table into Power BI.

Thanks for your help.

Dave

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

Poojara_D12 — Thu, 13 Feb 2025 05:55:33 GMT

HI DaveW31

Your current DAX formula for Current Record Indicator works in Import Mode because calculated columns are evaluated once during data load. However, DirectQuery Mode does not support row-by-row evaluation in the same way. Instead, you should replace the calculated column with a measure, since measures are evaluated dynamically at query time.

Since DirectQuery cannot use ALL in the same way for row context evaluation, you can rewrite it as:

Current Record Indicator = VAR MD = CALCULATE ( MAX ( 'Pharmacy Claims'[Invoice Date] ), ALLEXCEPT ( 'Pharmacy Claims', 'Pharmacy Claims'[Transaction ID] ) ) RETURN IF ( SELECTEDVALUE ( 'Pharmacy Claims'[Invoice Date] ) = MD, "Y", "N" )

Since DirectQuery does not support row-wise operations in calculated columns, using a measure is the best approach.

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

Anonymous — Thu, 13 Feb 2025 06:11:34 GMT

Hi DaveW31 ,

As per your last reply, it appears that you would like to retrieve the column [Current Record Indicator] directly from the data source SQL side. To achieve this, you can use the following SQL statement:

SELECT [Transaction ID], [Invoice Date], CASE WHEN [Invoice Date] = MAX([Invoice Date]) OVER (PARTITION BY [Transaction ID]) THEN 'Y' ELSE 'N' END AS [Current Record Indicator] FROM [Pharmacy Claims]

OVER Clause (Transact-SQL) - SQL Server | Microsoft Learn

How to Use the SQL PARTITION BY With OVER

Best Regards

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Mon, 17 Feb 2025 17:18:17 GMT

Poojara,

The script keeps timing out, not sure what is going on with it. I'm still looking at it.

Thanks,

Dave

Re: Problems with CALCULATE and ALL when going from Import to DirectQuery

DaveW31 — Mon, 17 Feb 2025 17:19:55 GMT

Shahariar,

I tried adding the measure and it keeps timing out, not sure what is going on with it. I'm still looking at it.

Thanks,

Dave