Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Exclude nulls and whites from the calculation

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 [...
Need Help
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    9 years ago

    Hi Anonymous,

     

    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

     

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
9 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    vanessafvg

    yes

    • v-qiuyu-msft's avatar
      v-qiuyu-msft
      Icon for Community Support rankCommunity Support
      9 years ago

      Hi Anonymous,

       

      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