SELECT statement with multiple criteria?

SpaceCoyote
Participant III

Hello,

I’m trying to create an initial value from a table that uses multiple criteria.

Table example (Sales):
Customer | Promo | Available Qty
Steve | Apple | 2
Steve | Pear | 3

Something like SELECT(Sales[Available Qty], [Customer] = [_THISROW].[Customer], [Promo] = [_THISROW].[Promo]))

So, it would create a single initial numerical value of 2 for Steve if the promo was Apple, and 3 if the promo was Pear

It doesn’t seem to work that way, and I also tried using nested SELECT statements. Any ideas?

Thanks,

Chris

0 3 1,040
3 REPLIES 3

You just need to add an AND function and don’t forget to use ANY to get a single value (SELECT returns a list), resulting:

ANY(SELECT(Sales[Available Qty], AND([Customer] = [_THISROW].[Customer], [Promo] = [_THISROW].[Promo])))

Perfect! Thanks, Fernando. I tried the AND, but I forgot that it goes before the statement and not in the middle. Nice tip on the use of ANY.

Chris


As attached pic ,
If you want list : put " select " in valid if
If you want a single value : put " ANY " before " SELECT " in App formula

Top Labels in this Space