topic Re: SUMX Function in Desktop

SUMX Function

ashwani_pal — Wed, 28 Feb 2018 06:21:32 GMT

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

Re: SUMX Function

Anonymous — Wed, 28 Feb 2018 06:31:35 GMT

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.

Re: SUMX Function

ashwani_pal — Wed, 28 Feb 2018 08:54:01 GMT

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

Re: SUMX Function

Anonymous — Wed, 28 Feb 2018 23:51:40 GMT

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.

Re: SUMX Function

Ashish_Mathur — Thu, 01 Mar 2018 03:29:56 GMT

Hi,

Share some data and show the expected result.

Re: SUMX Function

v-jiascu-msft — Thu, 01 Mar 2018 06:06:11 GMT

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

Re: SUMX Function

v-jiascu-msft — Tue, 13 Mar 2018 00:54:51 GMT

Hi ashwani_pal,

Could you please mark the proper answer as solution?

Best Regards!
Dale

Re: SUMX Function

ashwani_pal — Tue, 13 Mar 2018 10:02:05 GMT

Thanks everyone for guiding.