PowerBI_variables_error message "cannot convert value of type Text to type TRUE/FALSE"

1
votes

In PowerBI I have a table like this:

enter image description here

I created a measure to calculate the total head count for selected groups and selected age:

head count_total = 
VAR
  Category = FILTER(ALL('table'[group]), 'table'[group] = "apple" || "pear")
VAR
  includeage = FILTER(ALL('table'[age]), table[age]>=20)
RETURN
  CALCULATE(SUM('table'[head count]), Category, includeage)

I get this error message:

Cannot convert value “pear” of type Text to type TRUE/FALSE

Can anyone help diagnose where went wrong in the coding? Many thanks!

1
variablespowerbi

1 Answers

1
votes

The expression 'table'[group] = "apple" || "pear" is not correct. It compares table value with "apple", and the boolean result from this operation is "or"-ed with "pear". In this context "pear" should be a boolean value, while it is a text. Thus the error message. The correct expression in this case is 'table'[group] = "apple" || 'table'[group] = "pear" or 'table'[group] in { "apple" , "pear" }, so the measure should be (adding quotes around one table reference):

head count_total = 
VAR
  Category = FILTER(ALL('table'[group]), 'table'[group] = "apple" || 'table'[group] = "pear")
VAR
  includeage = FILTER(ALL('table'[age]), 'table'[age]>=20)
RETURN
  CALCULATE(SUM('table'[head count]), Category, includeage)