Problems with CALCULATE and ALL when going from Import to DirectQuery

[DaveW31]

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.

[Poojara_D12 · Accepted solution]

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.

[Poojara_D12 · Accepted solution]

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.

[DaveW31]

Poojara,

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

Thanks,

Dave

[shafiz_p]

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

[DaveW31]

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

[DaveW31]

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

[Anonymous]

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

[DaveW31]

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

Thanks for your help.

[Anonymous · Accepted solution]

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

[amarma]

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!

[DaveW31]

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" ) )

in

   #"Added Custom1"

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

Thanks again.