Match function

[P_S]

Hello, I am trying to use match function but the result is not what I expected. I am using this

If(Match(name, 'X', 'Y'),
Sum([Cost])
)

When I try to select the "X" name the result sum show the sum cost of "X" and the same result if I select "Y". But if I select "X" and "Y" the result will show as "-" I also change the expression to

If(name = 'X' and name = 'Y'),
Sum([Cost])
)

If(name = 'X' or name = 'Y'),
Sum([Cost])
)

The result still the same. Could you help me?

Any advice will be greatly appreciated

[Channa]

Use wildmatch

Match returns true false case

[P_S]

Thank you for your advice but after I try the result still the same.

If(wildmatch(name, 'X, 'Y'),
Sum([Cost])
)

If I selected only X or Y the result will show but if I selected X and Y the result will be - again

[Channa]

Ur missing single cote on top of X

'X' 

[P_S]

Oh, thanks but after I fix the expression it still the same. If select two variables the result will not show.

[Channa]

May you have null values
Check data

[SergeyMak]

Hi!

It all depends where you use it.

If you create a UI table with name as dimension, then your formula works. It also works in Load script, because the comparison happens for every row. If you try to use it without dimension, then you need to use aggregation function for name eg 

If(Match(MaxString(name), 'X', 'Y'),
Sum([Cost])
)

[jonathandienst · Accepted solution]

The expression using [name] without an aggregation function (known as a 'naked field name') can only return a value if there is only one possible value for [name]. Otherwise it will return null. The behaviour of a naked field is the same as one wrapped in Only(...).

If you want to sum for 'X' or 'Y', but want to respect user selections as well, then you could use something like one of these:

=Sum({<name *= {'X', 'Y'}>} Cost)    // intersection of user selections and 'X', 'Y' selection
or
=Sum({<name += {'X', 'Y'}>} Cost)    // union of user selections and 'X', 'Y' selection

[P_S]

Many thanks to all of you for good advice.