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,

 

Please try this:

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

Hope this helps.

 

Best Regards,

Giotto Zhi

 

  • Lorenz33's avatar
    Lorenz33
    Helper IV
    6 years ago

    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'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?