SUMMARIZE, GROUP BY without generate calculated table

[Earkkaperk]

Hi,

I need you guys help with this topic.

First, my datasource is the dataset that was provided with direct query.

I successfully find the max value group by datekey & skuid. It went well if I created new calculated table with this DAX.

MaxPlanned = SUMMARIZE('Fact Product Details Shift View','Fact Product Details Shift View'[DateKey],'Fact Product Details Shift View'[skuId],"Planned",MAX('Fact Product Details Shift View'[Planned]))

The highlighted column is the right one.

But when I publish to Service, it is failed to refresh because there is a calculated table in it as below error.

My question is, Is there any ways to do the summarize or groupby without create a calculated table?

or is there anyway to avoid this refresh error?

As I know, I don't have right to modify anything in their dataset.

Thanks.

[amitchandak · Accepted solution]

@Earkkaperk , You can use measures if needed

MaxPlanned = Maxx( SUMMARIZE('Fact Product Details Shift View','Fact Product Details Shift View'[DateKey],'Fact Product Details Shift View'[skuId],"Planned",MAX('Fact Product Details Shift View'[Planned])), [Planned])

If needed you can use sumx

Also, calculated table should work after publish.

[Earkkaperk]

Hi,

Thank you. It solves my long long problem 🙂

for calculated table, I have tried published but it failed as error below for your information.

Data source error: {"error":{"code":"Premium_ASWL_Error","pbi.error":{"code":"Premium_ASWL_Error","parameters":{},"details":[{"code":"Premium_ASWL_Error_Details_Label","detail":{"type":1,"value":"We cannot refresh this dataset because the dataset contains calculated tables or calculated columns based on data from a Single Sign-on (SSO)-enabled Direct Query data source. Please configure the dataset to use an explicit connection with granular access control to access this data source and then try again."}}],"exceptionCulprit":1}}}
Cluster URI: WABI-NORTH-EUROPE-redirect.analysis.windows.net
Activity ID: dd0faf50-5235-485f-b220-a7924b1dd486
Request ID: 78662da1-ddbc-4e13-bd3f-1aa604fcd179
Time: 2024-05-31 03:17:20Z