Forum Discussion

Photoguy190972's avatar
Photoguy190972
Regular Visitor
5 years ago

Refresh Issues

Im running a direct query with a DAX table where I'm applying some logic to sum data in different fields.  I've removed and summarized data, I'm only createing a table that sums fields based on some criteria.  See the DAX here:

 

Orders Uploaded =
ADDCOLUMNS(
'Image_Counts',
"Orders Uploaded", DISTINCTCOUNT (Image_Counts[photocode]),
"HR Images Uploaded", Image_Counts[hr_image_count],
"LR Images Uploaded", Image_Counts[hr_image_count],
"Last 30 Mins", if(Datediff(Image_Counts[created], Image_Counts[DateTimeUTC],MINUTE)<30,Image_Counts[hr_image_count],0),
"Last 60 Mins", if(Datediff(Image_Counts[created], Image_Counts[DateTimeUTC],MINUTE)<60,Image_Counts[hr_image_count],0),
"Last 90 Mins", if(Datediff(Image_Counts[created], Image_Counts[DateTimeUTC],MINUTE)<90,Image_Counts[hr_image_count],0),
"Last 120 Mins", if(Datediff(Image_Counts[created], Image_Counts[DateTimeUTC],MINUTE)<120,Image_Counts[hr_image_count],0),
"Images Today", If(DATEDIFF(Image_Counts[created]+(Image_Counts[DateTimeLocal]-Image_Counts[DateTimeUTC]),Image_Counts[DateTimeLocal],DAY)<1,Image_Counts[hr_image_count],0),
"IsToday", If(DATEDIFF(Image_Counts[created]+(Image_Counts[DateTimeLocal]-Image_Counts[DateTimeUTC]),Image_Counts[DateTimeLocal],DAY)<1,TRUE,FALSE),
"CreatedOnTimeLocal", (Image_Counts[created] + (Image_Counts[DateTimeLocal]-Image_Counts[DateTimeUTC])
))

I'm getting the following when refreshing the data in the service:

 

I've filtered records to a single day (can be up to 150k on a busy day). I've removed all fields that are not required.  Not sure what I have to do to solve this.  I'm new to PBI and DAX so any tips woud be greatly appreciated.
Need Help

5 Replies

  • GilbertQ's avatar
    GilbertQ
    Super User
    5 years ago

    Hi Photoguy190972 

     

    If you are using a calculated table as far as I am aware that will not refresh in DirectQuery because it has to pre-compute the calculated table.

     

    The only option is to have a query to the underlying source in PowerQuery for the DirectQuery to complete.

  • GilbertQ's avatar
    GilbertQ
    Super User
    5 years ago

    Hi Photoguy190972 

     

    If you create this table in Power BI Desktop and then use DAX Studio you could see how much memory the table is consuming as well as how many rows it is using.


    Here are more details on how to achieve this: How to use Vertipaq Analyzer with DAX Studio for Power BI Model analysis - Reporting/Analytics Made easy with FourMoo and Power BI

     

    I think that the table is consuming too much memory and I would suggest creating the table in Power Query so that it can be compressed on the way into the dataset.

    • Photoguy190972's avatar
      Photoguy190972
      Regular Visitor
      5 years ago

      Hey there, thanks so much for your tip!  I followed your advice and have added the new table in power query and I'm not getting the error.  However, the data is not updating when I click on the refresh button in the report.  I have tried updating in desktop then in the service and even if I wait 15 mins nothing updates.   Unless I go into Lineage view and Refresh Now, it won't update like my other Direct Query reports.  Any help will be greatly appreciated.

  • GilbertQ's avatar
    GilbertQ
    Super User
    5 years ago

    Hi Photoguy190972 

     

    You will need to refresh the dataset and not the refresh button in the report page.

     

    Go into your dataset and click on Refresh Now as shown below.

     

    • Photoguy190972's avatar
      Photoguy190972
      Regular Visitor
      5 years ago

      Yes, thanks but I'm aware of that.  I thought Direct Query would update upon interaction though?  This works with other queries I'm working with.  I don't want users to have to go in and manually refresh the data as they are monitoring activity.