DAX Formula Compatibility Limitation

[Lorenz33]

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.

[v-gizhi-msft]

Hi,

Please try this:

ratio = SUM('Table'[value])/CALCULATE(SUM('Table'[value]),ALLSELECT(Table))

Hope this helps.

Best Regards,

Giotto Zhi

[Lorenz33]

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.

[v-gizhi-msft]

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

[Lorenz33]

Hi Giotto,

Sorry for replying so late. In my testing I found that when I do:

measure = SUM(Table[Value2)

it returns the correct sum.

But when I do:

measure = CALCULATE(SUM('Table'[Value2]),ALL(Table))

I get a much greater value. Is it because the ALL statement might be summing that column for the entire table, instead of the filtered subset I am working with?

[v-gizhi-msft · Accepted solution]

Hi,

Please try this:

ratio = SUM('Table'[value])/CALCULATE(SUM('Table'[value]),ALLSELECTED(Table))

Best Regards,

Giotto Zhi