Forum Discussion
Running total in DirectQuery / Dual Mode refresh
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.
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])
)
)
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 😄 ) ?
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.
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
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