Can't use New measure in Power Pivot

Question

Usually when I import data from a table I won't be able to use the fields directly when I'm creating "New Measure". I usually get the following error: "Calculation error in measure 'Range'[measure 1]: A single value for column 'S11' in table 'Range' 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 following trick helps me to get around the error:

%Show text values in PowerPivot, where Range is table name and S11 is column reference.
=CALCULATE(
    VALUES(Range[S11]);
    FILTER(Range;
    COUNTROWS(VALUES(Range[S11]))=1))

Is it possible to import data so I can use it directly without manipulate it?

RADO RADO · Accepted Answer · 2018-08-05T08:25:51

Your "work-around"formula can be simpler:

My Text = IF(HASONEVALUE(Range[S11]), VALUES(Range[S11]))

However, instead of "getting around an error" you should fix it. If I understand correctly what you are trying to achieve, to create a measure that sums two columns in a table:

Sum of 2 columns = SUMX( 'Range', Range[Column1] + Range[Column2])

The formula you currently have for measure 1 would only work if you use it to create a calculated column. If you attempt to use it in a measure, you will always get an error. To understand why, you need to learn the concept of "Row Context" in DAX:

Row and Filter Context

Can't use New measure in Power Pivot

1 Answers