Direct Query and Summarize workaround

[IoanCosmin]

I am having a hard time finding a way to summarize sevaral direct query tables based on some measures. What's really gets me is that, if I create the summarized table (either with SUMMARIZE() or SUMMARIZECOLUMNS()) and then publish it, it actually works in the PBI Service. The problem comes when I try to open the .pbix file or download it from the service to make additional changes - Something went wrong: Object reference not set to an instance of an object. Assuming that's because DQ is not compatible with summarize. 

My tables have completely different columns and measures based on various rates/dates.  

Consolidated = 
VAR LocationX = SUMMARIZE(Date,
    Date[Calendar Year], Date[Calendar Month],,
    "Revenue",[Total Revenue (LocationX)],
    "Gross Profit",[Gross Profit (LocationX)],
    "Efficiency",[Efficiency (LocationX)])

VAR LocationY = SUMMARIZE(Date,
    Date[Calendar Year], Date[Calendar Month],,
    "Revenue",[Total Revenue (LocationY)],
    "Gross Profit",[Gross Profit (LocationY)],
    "Efficiency",[Efficiency (LocationY)])
RETURN  
    UNION(LocationX, 
    LocationY)

How can I get around this without switching the tables to Import mode. I've looked into defining table views to use as aggregates but I'm not sure that's going to help or I cannot fully understand it. There has to be a way to do something about this. What would you guys suggest?

Many thanks

[dax]

Hi @IoanCosmin , 

I test this in my environment(connect to sql, direct query, desktop 2019 Dec), which works well. So what is your current version of your desktop? AND what is your connector in your pbix file? You could try new version of desktop(I also will try this in new version later) to see whether it work or not. And you need to make sure you didn't lost anything in youir file , you could re-create a report to see whetehr it work or not.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[IoanCosmin]

Hello Zoe,

Thank you very much for taking the time to test it for me. I am running March 2020 version and the built in SQL Server connector. The report that I am speaking about is recreated. Are you sure it still works after you close the file and try to open it again? Not sure what else to try. Since SUMMARIZE is not compatible with direct query, I think that's what is causing the problem. https://docs.microsoft.com/en-us/analysis-services/tabular-models/dax-formula-compatibility-in-direc... 

Not sure what else to try. What would you suggest?

Thank you,

C

[rivo201]

Hello! 
I had same issue where SUMMARIZE was not possible to use and for me worked "CROSSJOIN"

SUMMARIZE(
  FILTER(
     CROSSJOIN(
       {DIRECT_QUERY_TABLE} , {OHTER_REQUIRED_TABLES}
     )  
     , {Create relations between Crossjoined tables}
   )
  , {Do calculations}
)