Forum Discussion

crisc's avatar
crisc
Helper I
6 years ago
Solved

Running total in DirectQuery / Dual Mode refresh

Hello everyone,

 

we are facing a problem with a small report based on DirectQuery.

The report contains two tables:

  1. Dimension table (Imported)
  2. Fact table (DirectQuery)

A key element of the report was a running total visual based on a datetime column.

 

Running Total = 
CALCULATE(
    [Measure];
    FILTER(
        ALLSELECTED(DirectQueryTable);
        DirectQueryTable[DatetimeColumn]<=MAX(DirectQueryTable[DatetimeColumn]
    )
)

 

This measures worked when i first created the report - due to changes on the SQL Database i had to rework this report and in this process my problem occurred.

Now this measure always returns a blank value (it surely didn't at first).

I am sure this is not a problem with the changes of the Database but with the DirectQuery itself.

 

While doing some research i stumbled over the storage modes and changing the Fact table from "DirectQuery" to "Dual Mode" solves my problem with the measure BUT if i publish the report with this storage mode, all data only gets pull with a scheduled refresh - this nullifies the whole idea of the report.

 

Basically i need either the measure (or a equivalent of it) to work in DirectQuery or the Dual Mode to pull data from the SQL Server like a DirectQuery does.

 

Can anyone help me with this / give me a hint in the right direction here?

 

Thank you very much!

Chris

Need Help
  • d_gosbell's avatar
    d_gosbell
    6 years ago

    If it was a user rights issue I would not expect normal non-cummulative measures to work either. So if straight sum or count based measures work then I doubt this is the issue.

     

    I created a DirectQuery model against a local copy of the AdventureWorks sample database and both the patterns I posted worked without issue even when adding slicers from other tables. So I'm guessing that it must be something in your database or something else in your report.

     

    To trouble shoot this I would suggest either using the Performance Analyzer in Power BI Desktop or the All Queries trace in DAX Studio to capture the DAX generated by the visual with your cummulative measure. Then you could re-run the query in DAX Studio (while connected to your DirectQuery model in Power BI Desktop) this time using the Server Timings trace. This will show you the one or more SQL queries that the DAX query generates in order to produce the results. Then you can play around with those queries to see if you can find a hint as to the root cause of this issue. 

7 Replies

  • Icey's avatar
    Icey
    Community Support
    6 years ago

    Hi crisc ,

     

    To my knowledge, Dual mode can't pull data as DirectQuery mode does.

    Maybe d_gosbell could help you. He answered a similar post: Dual Storage Mode - not acting as direct query.

     
     

    Best Regards,

    Icey

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • crisc's avatar
      crisc
      Helper I
      6 years ago

      Hi Icey ,

       

      thanks for your help, the post gives me a better idea of how dual storage mode works. Sadly i think it's not suitable for my report.

      Is there a way to create a running total using direct query then?

       

      My attempts so far are:

      • Using a measure like the one above (ALLSELECTED() & MAX())
      • Using the same measure but with a filter statement like this: 

       

          FILTER(
        ALLSELECTED(DirectQueryTable);
        ISONORAFTER(
            DirectQueryTable[DatetimeColumn];
            MAX(DirectQueryTable[DatetimeColumn]);
            DESC
        )
    )​

       

      • Forcing the direct query into a new table with NewTable = DirectQueryTable and using NewTable for my calculations

      I feel like in DirectQuery ALLSELECTED() does not work the way it used to anymore... but i was not able to find any documentation about this.

      None of these work and i get the idea why, but i can't figure a way out to get around this.

       

      Thanks

      Chris

      • d_gosbell's avatar
        d_gosbell
        Super User
        6 years ago

        If you don't want to do periodic refreshes of the data then you definitely should not use any storage mode other than DirectQuery

         

        crisc wrote:

        I feel like in DirectQuery ALLSELECTED() does not work the way it used to anymore... but i was not able to find any documentation about this.

         

        I'm not aware of anything having changed with ALLSELECTED and without a working example of the calc it's hard to say what has happened here. However this is an unusual way to use ALLSELECTED and not a typical way of doing a running total.

         

        Have you tried a pattern like the following?

        Running Total = 
CALCULATE(
    [Measure];
    FILTER(
        ALL(DirectQueryTable[DatetimeColumn]);
        DirectQueryTable[DatetimeColumn]<=MAX(DirectQueryTable[DatetimeColumn])
    )
)

         

        Typically when I build my models I have a separate date table to make date calculations easier and in this case I build a running total as follows:

        Running Total = 
CALCULATE(
    [Measure];
    FILTER(
        ALL(DateTable);
        DateTable[DateColumn]<=MAX(DateTable[DateColumn])
    )
)