Forum Discussion

Lorenz33's avatar
Lorenz33
Helper IV
6 years ago
Solved

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...
General Comment
Need Help
Tips and Tricks
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please try this:

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

     

    Best Regards,

    Giotto Zhi 

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

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's avatar
Lorenz33
Helper IV
6 years ago

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's avatar
    v-gizhi-msft
    Community Support
    6 years ago

    Hi,

     

    Please try this:

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

     

    Best Regards,

    Giotto Zhi