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. 

crisc's avatar
crisc
Helper I
6 years ago

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's avatar
crisc
Helper I
6 years ago

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