SUMX Function

[ashwani_pal]

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

[ashwani_pal]

Thanks everyone for guiding. 

[v-jiascu-msft]

Hi @ashwani_pal,

Could you please mark the proper answer as solution?

Best Regards!
Dale

[v-jiascu-msft · Accepted solution]

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.

Best Regards,

Dale

[Anonymous]

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]

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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.