Category Segmentation (Range Buckets) using direct query

[anil]

My dataset has ID's with multiple orders. I am trying to calculate average discount across orders per ID.

And creating a column to bucket the averages of ID's. 

Bucket = Switch ( True(),
                           And( [Avg Discount %]>=-10 ,[Avg Discount %] < Sample[Fixed TMax Var 5%]) , "Green",
                           And( [Avg Discount %]>=Sample[Fixed TMax Var 5%],[Avg Discount %] < Sample[Fixed TMax Var 15%]) , "Yellow",
                           And(  [Avg Discount %] >= Sample[Fixed TMax Var 15%],[Avg Discount %] < 500),  "Red"
                         )

But when I am using the same formula to create a column. I am getting error "Function 'SUM' is not allowed as part of calculated column DAX expressions on DirectQuery models."

     I want to create a bar chart which shows the count of ID's in each segment. is there any alternate ways to achieve this? 

[v-frfei-msft · Accepted solution]

Hi @anil,

This is DAX Formula Compatibility limitation. When using DirectQuery mode, aggregation functions like COUNT, SUM, etc are supported in measure only. For more details, please see: DAX Formula Compatibility in DirectQuery Mode. You can switch the table from direct query to import to work around.

Regards,

Frank

[anil]

Hi @v-frfei-msft

     Thanks for the reply. But we wanted to create the reports using direct query mode. As the data set, we are working is very huge with constant updates. As power bi import mode data limit is 10 GB only we didn't want to go with import mode.

[v-frfei-msft]

Hi @anil,

Maybe you can use composite models to make the Storage Model to be Mixed. For more details, please check the online document.

Regards,

Frank