Forum Discussion

ashwani_pal's avatar
ashwani_pal
Helper I
8 years ago
Solved

SUMX Function

Hi, 

 

I am using Sumx Function  to calculate selective data in Power BI report using SUMX as a query in  adding custom column. But It is showing error "Expression.Error: The name 'SUMX' wasn't recognized.  Make sure it's spelled correctly". What is needed to be done in such case.

 

Thanks

 

Regards

Ashwani

Need Help
Tutorial Requests
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi ashwani_pal,

     

    Seems you were using a DAX function in the Query Editor that only accepts Power Query formula language (M language) and R. Please try it out in the Data pane.

    SUMX_Function

     

    Best Regards,

    Dale

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Can you provide some more information, such as the a brief description of the dataset and the formula you are trying to write?  I'm expecting you are using SUMX in the wrong context.

    • ashwani_pal's avatar
      ashwani_pal
      Helper I
      8 years ago

      Thanks for quick reply,

      I need to filter columns from 2 different tables by compairing there values and whichever rows are remaining after filtering, i need to aplly mathematical operation (summation.) Query i am using is : 

       

       

      sumx(filter("ERTTReports_RuntimeLifetime_SHIFT", "ERTTReports_RuntimeLifetime_SHIFT.Asset" = [Asset], AND
      ( "ERTTReports_RuntimeLifetime_SHIFT.StartTime" >=[Time],"ERTTReports_RuntimeLifetime_SHIFT.StartTime" <=[Next Time]),
      ("ERTTReports_RuntimeLifetime_SHIFT.EndTime">=[Time]and "ERTTReports_RuntimeLifetime_SHIFT.EndTime"<=[Next Time])),
      "ERTTReports_RuntimeLifetime_SHIFT.Duration")

       

      Regards

      Ashwani

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Sounds like the issue is trying to get dax to do everything in a single statement and over complicating the problem.

         

        Firstly, are you able to join your two tables via the Asset column?  If not, are you able to create a Unique Asset list (can be done within Power Query) that you can link both tables to instead?  Once you get your data structure right, we can create a set of dax statements to break your formula into chunks and have Power BI run them appropriately.

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    Hi ashwani_pal,

     

    Seems you were using a DAX function in the Query Editor that only accepts Power Query formula language (M language) and R. Please try it out in the Data pane.

    SUMX_Function

     

    Best Regards,

    Dale