Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Use wildmatch
Match returns true false case
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
Ur missing single cote on top of X
'X'
Oh, thanks but after I fix the expression it still the same. If select two variables the result will not show.
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])
)
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
Many thanks to all of you for good advice.