Power BI/DAX error message using SUMX: A single value for column 'Column' in table 'Table'

[pstaggers]

I am attempting to use SUMX to get a correct total in my PBI visualization. This is the DAX I am trying to implement, but I get this error:

Test Time Total =
SUMX(VALUES('Table1'[Unique ID]),'Table2'[Test Time])

A single value for column 'Test Time' in table 'Table2' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

The two tables are tied with a one-to-many relationship with the cross filter set to both directions.

Is there a good way to troubleshoot this error?

[amitchandak · Accepted solution]

@pstaggers , As you have use values you need give a measure

SUMX(VALUES('Table1'[Unique ID]),calculate(Sum('Table2'[Test Time])))

or

SUMX(VALUES('Table1'[Unique ID]),calculate(Max('Table2'[Test Time])))

[Pragati11]

HI @pstaggers ,

For which column are you trying to SUM the values? 'Table1'[Unique ID] or 'Table2'[Test Time] ??

Thanks,

Pragati

[pstaggers]

Hi Pragat,

I am trying to SUM the Test Time.  The Unique ID is a unique identifier for a single test that takes a set amount of time.  If I had 3 Unique IDs with 8 hours, 12 hours, and 16 hours, respectively, I would want to sum up to 36 hours of test time.

Thanks

[amitchandak · Accepted solution]

@pstaggers , As you have use values you need give a measure

SUMX(VALUES('Table1'[Unique ID]),calculate(Sum('Table2'[Test Time])))

or

SUMX(VALUES('Table1'[Unique ID]),calculate(Max('Table2'[Test Time])))

[pstaggers · Accepted solution]

Thank you @amitchandak .  That did the trick!  Both worked since it is only evalutating a single value (SUM and MAX yield same result, MIN works too)

Curious, but is VALUES really needed?  Maybe a simpler solution without?