Dax formula won't run in direct query mode

[lauriemclolo]

This formula gets my starting balance for a monthly total of points.  When I try to add the measure in Direct Query mode, it just spins and cannot run.  When I switch to Import, it runs fast and just fine.   However, I am directed to use Direct Query.  Can you help me figure out a solution?

Starting Balance2 =

VAR MaxDate = MAX ( v_point_aggrr[period_begin_date] ) -- Saves the last visible date

RETURN

    CALCULATE (

         [Ttl Points Earned2]+[Ttl Points Returned2]+[Ttl Points Redeemed2]+[Ttl Points Expired2]

,        

        v_flt_point_aggrr[period_begin_date] <= MaxDate-1,   -- Where date is before the last visible date

        ALL ( v_point_aggrr[period_begin_date])               -- Removes any other filters from Date

    )

[v-nmadadi-msft]

Hi @lauriemclolo 

As we haven’t heard back from you, we wanted to kindly follow up to check if the suggestions  provided by the community members for the issue worked. Please feel free to contact us if you have any further questions.

Thanks and regards

[v-nmadadi-msft]

Hi @lauriemclolo 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @lauriemclolo 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[johnt75]

I don't think you need the ALL. Any existing filters on the date will be overwritten because you are supplying an explicit filter on that column. I would also rewrite the filter condition to check for simply less than the max date, rather than less than or equal to the max date -1. I don't know if that would have any impact, but if you are using DirectQuery then you need to use any performance boost you can, no matter how small.

Try

Starting Balance2 =
VAR MaxDate =
    MAX ( v_point_aggrr[period_begin_date] ) -- Saves the last visible date
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        v_flt_point_aggrr[period_begin_date] < MaxDate -- Where date is before the last visible date
    )

[v-nmadadi-msft]

Hi  @lauriemclolo ,
Thanks for reaching out to the Microsoft fabric community forum.

There are some limitations with DirectQuery

DirectQuery in Power BI - Power BI | Microsoft Learn
Please check if any of the reasons mentioned in the document is the reason for the error you are experiencing.
Additionally keep in mind that at least initially, limit measures to simple aggregates. If the measures operate in a satisfactory manner, you can define more complex measures, but pay attention to performance.

I hope this information helps. Please do let us know if you have any further queries.
Thank you

[kushanNa]

Hi @lauriemclolo 

It’s very difficult to determine what’s going wrong in your query without reviewing your data and other measures. I suggest first checking whether the other measures related to this one run without issues in DirectQuery mode. and try to avoid using functions that have limitations in DirectQuery mode. On the Microsoft Learn documentation pages, under the Remarks section, you can see whether a function has such limitations. Most DirectQuery limitations apply to calculated columns, but it’s better to avoid those functions in measures as well if possible. 

[Ahmed-Elfeel]

Hi lauriemclolo,

What you are seeing is expected. I mean the formula works fine in Import mode because Vertipaq is in memory but in directquery every filter must translate into SQL and patterns like ALL() + < MaxDate-1 usually break performance .

So Try this if not work reply to me again:

1-Replace ALL() with REMOVEFILTERS() (Sometimes REMOVEFILTERS works if ALL() is interrupt or confused)

Starting Balance2 =
VAR MaxDate = MAX ( v_point_aggrr[period_begin_date] )
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        FILTER (
            REMOVEFILTERS ( v_point_aggrr ),
            v_point_aggrr[period_begin_date] < MaxDate
        )
    )

2-Try wrapping all conditions in FILTER() (This also works fine so try it first)

Starting Balance2 =
VAR MaxDate = MAX ( v_point_aggrr[period_begin_date] )
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        FILTER (
            v_point_aggrr,
            v_point_aggrr[period_begin_date] < MaxDate
        )
    )

3. Use a separate Date table (you can google it)

Starting Balance2 =
VAR MaxDate = MAX ( 'Date'[period_begin_date] )
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        KEEPFILTERS ( 'Date'[period_begin_date] < MaxDate )
    )

 
Let me know if these is useful or not 🙂

[lauriemclolo]

Hello @Ahmed-Elfeel  I'm testing the formulas in import mode first before trying them in Direct Query.   

The first solution you provded gave me an "incorrect parameter type" error under the filter table portion.  The second solution works, but only on the total level (see image-I called the new formula "Starting Balance3"), not within the months (i.e. period_begin_date field).  The months give me blanks.  The period begin date is in the columns of my matrix.  Thank you.

[Ahmed-Elfeel]

Hi @lauriemclolo ,So First lets explain what happens:
1-In import mode all data are stored in Vertipaq memory (Any DAX Calculations are calculated inside Power bi So fast)
2-In direct query mode Power bi itself (It translate DAX into SQL and sends it to DB)
3-The issue here we used expensive Formulas Like ALl() +Max Date (this cause Full table Scan which makes the query loop and loop and loop.

So what is the Solution?
First lets try simplified DAX like rewrite the formula which power bi can translate it to SQL so we will reduce formulas like ALL() and FILTER()

Starting Balance2 =
VAR MaxDate = MAX ( v_point_aggrr[period_begin_date] )
RETURN
    CALCULATE (
        SUMX (
            v_point_aggrr,
            v_point_aggrr[Ttl Points Earned2] +
            v_point_aggrr[Ttl Points Returned2] +
            v_point_aggrr[Ttl Points Redeemed2] +
            v_point_aggrr[Ttl Points Expired2]
        ),
        FILTER (
            ALL ( v_point_aggrr ),
            v_point_aggrr[period_begin_date] < MaxDate
        )
    )

 Here we simplified it so SQL can translate it and reduce multiple subqueries.

and if you have access to DB you can oush the logic to the DB (Create a SQL view that calculates the starting balance using SQL. (I pulled all the rabbits out of my hat 😅)

I hope this can help you❤️

[lauriemclolo]

Hi @Ahmed-Elfeel   I had to remove the "accept as solution" because I realized the totals are not correct in that formula, though that version of the formula does run in Direct Query mode.  Below, the field StartingBalance2 is correct, but will not run in Direct Query mode.  

StartingBalance3 is incorrect, but WILL run in Direct Query mode.  Can you help me understand the difference between these two formulas?  Keeping in mind that all components in the formula (e.g. [Ttl Points Earned2] etc) are measures.

StartingBalance2=

VAR MaxDate = MAX ( v_flt_point_aggrr[period_begin_date] ) -- Saves the last visible date

RETURN

    CALCULATE (

         [Ttl Points Earned2]+[Ttl Points Returned2]+[Ttl Points Redeemed2]+[Ttl Points Expired2]

,        

        v_flt_point_aggrr[period_begin_date] <MaxDate,   -- Where date is before the last visible date

        ALL ( v_flt_point_aggrr[period_begin_date])               -- Removes any other filters from Date

    )

StartingBalance3=

VAR MaxDate = MAX ( v_flt_point_aggrr[period_begin_date] )

RETURN

    CALCULATE (

        SUMX (

            v_flt_point_aggrr,

            v_flt_point_aggrr[Ttl Points Earned2] +

            v_flt_point_aggrr[Ttl Points Returned2] +

            v_flt_point_aggrr[Ttl Points Redeemed2] +

            v_flt_point_aggrr[Ttl Points Expired2]

        ),

        FILTER (

            ALL ( v_flt_point_aggrr ),

            v_flt_point_aggrr[period_begin_date] < MaxDate

        )

    )

[Ahmed-Elfeel]

Hi @lauriemclolo,

So you want to know the difference between Measures and Columns !?

If yes....In short

• StartingBalance2 uses measures correctly:

[Ttl Points Earned2] + [Ttl Points Returned2] + ...etc  -- These are MEASURES

• StartingBalance3 incorrectly uses columns:

v_flt_point_aggrr[Ttl Points Earned2] + ...etc -- These are COLUMNS (which is incorrect!)

•  You are referencing columns instead of measures

- v_flt_point_aggrr[Ttl Points Earned2] ​

looks for a column in your table....but actually these are measures you have created

Also you can try this version (I edited it so give it a try)

StartingBalance3_Corrected =
VAR MaxDate = MAX ( v_flt_point_aggrr[period_begin_date] )
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        v_flt_point_aggrr[period_begin_date] < MaxDate,
        REMOVEFILTERS ( v_flt_point_aggrr[period_begin_date] )  
    )

Or use the Optimized Version (Using Date Table)

StartingBalance_DQOptimized =
VAR MaxDate = MAX ( 'Date'[Date] )  -- from your date table
RETURN
    CALCULATE (
        [Ttl Points Earned2] + [Ttl Points Returned2] + [Ttl Points Redeemed2] + [Ttl Points Expired2],
        'Date'[Date] < MaxDate,
        REMOVEFILTERS ( 'Date'[Date] )
    )

Let me know if you have another questions ☺️

[Ahmed-Elfeel]

Hi @lauriemclolo,
Np..i Just want to help not seeking solutions 😅❤️

Ok lets explain the difference here is that StartingBalance2 uses Measures while StartingBalance3 uses physical Columns

StartingBalance2 uses other Measures :

[Ttl Points Earned2] + [Ttl Points Returned2] + ...etc

While StartingBalance3 uses Table Columns :

v_flt_point_aggrr[Ttl Points Earned2] + v_flt_point_aggrr[Ttl Points Returned2] + ...etc

So let's break it in points:

• Using Measures (StartingBalance2) : DirectQuery must generate a separate SQL query to calculate each measure under the new filter and then add the results together (This creates multiple, and complex sub queries that often exceed DirectQuery's limitations)

• Using Columns (StartingBalance3) : The SUMX function can be translated into a single and more efficient SQL query that sums the values of those specific columns directly from the table (which is allowed)

in short: DirectQuery handles operations on columns better than operations on multiple measures.

I hope I helped you even a little 😄❤️.

[lauriemclolo]

okay.  Thank you for explainging the difference. And my sincere thanks for all the time helping me on this one formula.!!

What you explained is what I suspected.  However, as as I mentioned, those are measures, not columns, and the revision, [Starting Balance3] did not work.  It's creating a number that is far to large.  [Starting Balance2] gives the correct total.  I had suspected there was an issue with the measures, but when I replaced the measues (e.g. [Ttl Points Returned2] ) with the column from which is was derived (i.e. points_returned) it still didn't work.

Below is the formula I tried, replacing measures with columns, which still did NOT work.  

VAR MaxDate = MAX ( v_flt_point_aggrr[period_begin_date] )

RETURN

    CALCULATE (

        SUMX (

            v_flt_point_aggrr,

            v_flt_point_aggrr[points_earned] +

            v_flt_point_aggrr[points_returned] +

            v_flt_point_aggrr[points_redeemed] +

            v_flt_point_aggrr[points_expired]

        ),

        FILTER (

            ALL ( v_flt_point_aggrr ),

            v_flt_point_aggrr[period_begin_date] < MaxDate

        )

    )

[v-nmadadi-msft · Accepted solution]

Hi @lauriemclolo 

This function( ALL)  is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.
You can find the limitation in the remarks section of this article: ALL function (DAX) - DAX | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.
Thank you