IF to exclude values in dimension/table

[#1]

Hi

I am trying to make a pivot table that shows Names according to the below criteria:

Active = Y (so do not include N values)

Role = A or B (so do not include C values)

Type = N or V (so do not include E values)

The below if statement is what I used in the dimension but I still get values returning that include E values:

=if(Active='Y' and Role='A' or Role='B' and Type='N' or Type='V', Name)

Please can anyone assist?

Thanks!

[sunny_talwar · Accepted solution]

Try this with parenthesis:

=If(Active='Y' and (Role='A' or Role='B') and (Type='N' or Type='V'), Name)

[#3]

Hi Sunny, unfortunately it didn't work. Are ther any other ways to do this?

[sunny_talwar]

Not sure why it won't work, would you be able to share a sample?

[Kushal_Chawda]

=if( Active='Y'  and match(Role,'A','B') and match(Type,'N','V') , Name)

[#6]

I was doing it wrong!

I have another question. How do I do a count distinct in set analysis Im trying:

=count({<Active={'Y'}, Role={'A','B'}>}NoOfDaysOver30)

But I want to count distinct on name so it groups all the names together and gives me a score of 1 per name over 30 but this would also need to be based on latest date I believe