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 rec...
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

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.