Not In Filter expression

Question

Hi,
I'm trying to filter my dataset rows where I dont want my dataset to have rows with X,P
I m not able to find "Not in" operator to filter the dataset based on ResultType field

How would I do that?

Answer 1

HI Saqib,

Try the following by replacing Expression, Operator, and Value with the code snippet below. I am not sure how you want to handle the case where ResultType is neither, X, P, or NULL, so I treat it like NULL.

I hope this helps.

--Dan

Expression:

=Switch
 (
 Fields!ResultType.Value = "X", "Exclude",
 Fields!ResultType.Value = "P", "Exclude",
 IsNothing(Fields!ResultType.Value) = "Include",
 True, "Include"
 )

Operator: =
Value: Include

Answer 2

Hi @Saqib Mahmood ,
If we can not change the query, the most easy way for this scene would be adding another filter . Using two <> filter will resolve this . And we don't need to specify for NULL, it will be included if it exists in the dataset query result. See :

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi @Saqib Mahmood ，
For this you could either change the datasete query, add where clause :

SELECT *  
FROM table_name  
WHERE  ResultType NOT IN (X,P)  

Or you could in the report design tablix add visibility control , using expression as follow to hide those rows:

=IIF(Fields!ResultType.value="X" or Fields!ResultType.value="P" ,TRUE, FALSE )  

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @Joyzhao-MSFT ,

Forgot to mention that I also need values with ResultType with null. So basically:

I need: (ResultType <> X and ResultType <> Y) or ResultType is null

Answer 5

Hi @Saqib Mahmood ,

I think this query is suitable for your requirement, in this case we don't need to explicitly code for "OR ResultType IS NULL". It would be include in the result :

 SELECT *  
 FROM table_name  
 WHERE  ResultType NOT IN (X,P)  

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.