Exclude nulls and whites from the calculation

[vitexo87]

Hi, I need help to fix a measure I created, I need my measure to consider only the middle column values ​​that are not null or blank:

And my measure is this way today:

(IndicatorValue [Value]), Indicator [Name] = "IndicatorValue [Value]", IndicatorValue [Value], IndicatorValue [Value] Unidades_Rejeitos ")))

The value being presented is 1,053.00 being it should be only 551, how can I solve this?

[vanessafvg]

@vitexo87 you could try this

measure = calculate(IndicatorValue [Value], Filter(IndicatorValue,

                                                                        IndicatorValue [Description1] = "Unidades_Rejeitos "

                                                                        && not(isblank(description2))))

you will have to rework this slightly

[vitexo87]

@vanessafvg

I adapted your suggestion and tried it in two ways:

Follow:
Total of Units Rejected test = CALCULATE (sum (IndicatorValue [Value]); FILTER (Indicator; Indicator [Name] = "UnitsRejected" && not (isblank (Equipment)))

Error: "The expression references multiple columns. Multiple columns can not be converted to a scalar value."

and

Total of Units Rejected test = CALCULATE (sum (IndicatorValue [Value]); FILTER (Indicator; Indicator [Name] = "UnitsRejected" && not (isblank (Equipment [name]))))

Error: Unable to determine a unique value for the 'name' column in the 'Equipment' table. This can happen when a measurement formula refers to a column that contains many values, without specifying an aggregation such as min, max, cont, or sound to get a single result.

[vanessafvg]

@vitexo87 Do you have a relationship between these tables equipment and indicator?

[vitexo87]

@vanessafvg

yes

[v-qiuyu-msft · Accepted solution]

Hi @vitexo87,

Please add a MAX() function for the Equipment [name],

Total of Units Rejected test = CALCULATE (sum (IndicatorValue [Value]); FILTER (Indicator; Indicator [Name] = "UnitsRejected" && not (isblank (MAX(Equipment [name])))))

Best Regards,
Qiuyun Yu