Forum Discussion

Earkkaperk's avatar
Earkkaperk
Frequent Visitor
2 years ago
Solved

SUMMARIZE, GROUP BY without generate calculated table

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.

Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      Earkkaperk
      Frequent Visitor
      2 years ago

      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