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: Dimension table (Imported) Fact table (DirectQuery) A key element of the re...
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. 

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

     

    • crisc's avatar
      crisc
      Helper I
      6 years ago

      Hi d_gosbell  & thanks for your help! 

       

      d_gosbell wrote:

       

      Have you tried a pattern like the following?

       

       

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

       

       

       

      Yep, but it returns a blank value just like ALLSELECTED().

       

      d_gosbell wrote:

       

      Typically when I build my models I have a separate date table to make date calculations easier

      That's the way we do it in most of our reports aswell - i did it without an calendar/datetime table because i got only data from one day. I tried your approach nontheless: I created a time-table with every minute of the day in it, extracted the time from my DirectQuery Datetime Column and linked it to the new time-table.

       

      Sadly i get a blank value again with:

       

      d_gosbell wrote:

       

       

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

       

      Can there be an issue with my user rights on the new Database i mentioned... (i doubt that but i run out of ideas 😄 ) ?

      • d_gosbell's avatar
        d_gosbell
        Super User
        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.