Function 'SUMX' is not allowed as part of calculated column DAX expressions on DirectQuery model

[RCelur]

Hello,

We are creating a measure for Weighted Averages using SUMX function, and then based on the value from this measure we would like to bucket our dimension values (Business Units for example) into different categories. We were able to achieve this with Import mode by creating a calculated column, but now that we are trying to replicate the same in Direct Query mode we are unable to do so.

Error - Function 'SUMX' is not allowed as part of calculated column DAX expressions on DirectQuery model

Need help in fixing this issue.

Thanks in advance!

[amitchandak]

@RCelur , in direct query mode you will not be able to use earlier, filter in the column. So you need to create segmentation using a measure

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

refer what are few operations supported

https://www.youtube.com/watch?v=My0bLn9voo4&list=PLPaNVDMhUXGbKatyDdOhGbTL3xW2Xy6pA

[Greg_Deckler]

@RCelur - Well, there used to be an article that documented which DAX functions were supported for measures and calculated columns. https://msdn.microsoft.com/library/ee634396.aspx

But, apparently that document no longer exists. But, in general you can't create calculated columns in Direct Query unless you use a composite model, is that what you are doing? You should be able to use SUMX in a measure.

[Pragati11]

Hi @RCelur ,

SUMX function has got a limitation when used with Direct Query mode.

Check the folloiwng link:

https://docs.microsoft.com/en-us/dax/sumx-function-dax

Also, if you can share your SUMX calculation formula, we can suggest a different way of doing it.

One way could be to use SUM function with CALCULATE function.

Thanks,

Pragati