Forum Discussion
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. I need to use a SUM result in a new column.
I am creating a ratio based on the sum of all the values in one column and then applying it in a formula to each column. I tried OLAP, but Power Bi calculates the OLAP values before doing the filtering. Which returns the values for everything and not just my subset. Which is why I am creating the ratio as a measure and then trying to use it within a new column for each row.
Any help you can provide would be appreciated.
Hi,
Please try this:
ratio = SUM('Table'[value])/CALCULATE(SUM('Table'[value]),ALLSELECTED(Table))Best Regards,
Giotto Zhi
5 Replies
Hi,
Please try this:
ratio = SUM('Table'[value])/CALCULATE(SUM('Table'[value]),ALLSELECT(Table))Hope this helps.
Best Regards,
Giotto Zhi
Hello Giotto,
I am still getting the same error (Function SUM is not allowed as part of calculated column DAX expressions on DirectQuery models). However, I am making Ratio like this:
Ratio = SUM(Table[Value])/CALCULATE(SUM(Table[Value2]),Table)
Because Power Bi is not recognizing ALLSELECT. Where does ALLSELECT come from? Please let me know. Thanks.
Hi,
I am sorry it's my mistake.
Please try to create a measure first:
measure = CALCULATE(SUM('Table'[Value2]),ALL(Table))Then create a calculated column:
Ratio = SUM(Table[Value])/[measure]Hope this helps.
Best Regards,
Giotto Zhi
