Running total in DirectQuery / Dual Mode refresh

[crisc]

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

[Icey]

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]

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]

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]

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 · Accepted solution]

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. 

[crisc]

Hi @d_gosbell ,

thanks for all the effort you put into this, i really appreciate this!

I will keep searching for problems with the Database, i doubt that there is some other problem with my report since i recreated it from scratch and the same strange behaviour occurred.

To install DAX Studio i will need some permissions from our IT, so this will take a while but if i can pinpoint the problem i will let you guys know.

Thanks

Chris

[crisc]

Hi again,

using the Performance Analyzer i noticed some data type conversions in the query.

This led to a thread from @AudriusZ. 

It seems like there was the same problem he had with the decimals in the datetime2 format.

Here is what i tried & what worked in the end:

- Casting the datetime2 as datetime first, then as float (i was not able to cast it directly into float), then round it to 2 decimals, cast it back to datetime - Did not work

- Casting datetime2 as smalldatetime actually worked for me!

Thanks again!

Chris