If statement based on filter selection

[Kat92]

I want to show the number 6 if column Team= Team1 or Team2 or both and 7 if not.

How can I calculate that in set analysis?

I have tried the below and it works for Team1 or Team2 but not when both are selected

if(WildMatch(TEAM, 'Team1', 'Team2'), 6, 7)

Thanks in advance

[mruehl]

The problem is the selection of two values, not the formula itself.

Try:

IF(

MATCH(CONCAT( DISTINCT '|'&TEAM&'|'), '|Team1|', '|Team2|')

, 6, 7)

[Kat92]

Hi,

Thank you for the above suggestion, however I tried and when both are selected Team1 and Team2 it does not show the value 6 but again 7.

[Aasir]

if(Count({<TEAM = {'Team1', 'Team2'}>} distinct TEAM) > 0,6,7)

[Kat92]

This works, however it returns 6 also for the rest of the teams. So, 2 of the teams and their combinations need to return 6 while the rest 6 teams and any of their combinations should return 7

[Aasir]

My bad, try this 
if(WildMatch(TEAM, 'Team1', 'Team2') or WildMatch(TEAM, 'Team1|Team2'),6,7)

[Kat92]

It seems that 'Team1|Team2' is not recognized.

Is there maybe any update or other symbol to be used?

[vincent_ardiet_ · Accepted solution]

Like this?
7+(Count({<TEAM*=e({<TEAM={'Team1','Team2'}>}TEAM)>}TEAM)=0)

[Kat92]

Yes, this works perfectly.

Thank you very much!

[Aasir]

if(WildMatch(TEAM, 'Team1') or WildMatch(TEAM, 'Team2') or WildMatch(TEAM, 'Team1*Team2'),6,7)