IF any of the colums equal any of the value

[babajat]

Hello!

I've got this data:

ID	VALUE 1	VALUE 2	VALUE 3	VALUE 4
1	16	0	2	0
2	XYZ	0	0	0
3	0	0	0	0
4	0	0	XYZ	0
5	0	0	2	0
6	0	1	0	0
7	0	ABC	0	16
8	4	0	0	0
9	0	0	0	0
10	0	4	0	2
11	XYZ	1	0	XYZ
12	5	0	7	0
13	0	2	0	0
14	0	4	0	XYZ
15	ABC	0	3	0

I'm trying to add a new that return 1 if any of the "VALUE" columns is equal to "XYZ" or "1" or "3", if not it returns 0.

Like this one just to be clear:

ID	VALUE 1	VALUE 2	VALUE 3	VALUE 4	NEW COLUMN
1	16	0	2	0	0
2	XYZ	0	0	0	1
3	0	0	0	0	0
4	0	0	XYZ	0	1
5	0	0	2	0	0
6	0	1	0	0	1
7	0	ABC	0	16	0
8	4	0	0	0	0
9	0	0	0	0	0
10	0	4	0	2	0
11	XYZ	1	0	XYZ	1
12	5	0	7	0	0
13	0	2	0	0	0
14	0	4	0	XYZ	1
15	ABC	0	3	0	1

For now I have only managed to do it with this atrocious formula:

NEW COLUM = if('Report 1'[VALUE 1] = "XYZ" || 'Report 1'[VALUE 1] = "1" || 'Report 1'[VALUE 1] = "3"  || 'Report 1'[VALUE 2] = "XYZ" || 'Report 1'[VALUE 2] = "1" || 'Report 1'[VALUE 2] = "3" || 'Report 1'[VALUE 3] = "XYZ" || 'Report 1'[VALUE 3] = "1" || 'Report 1'[VALUE 3] = "3" || 'Report 1'[VALUE 4] = "XYZ" || 'Report 1'[VALUE 4] = "1" || 'Report 1'[VALUE 4] = "3", 1, 0)

It gets the job done but it also takes a long time to write and I have to write several ones like this one.

Is there a more efficient way to do it?

Thanks in advance.

[SpartaBI · Accepted solution]

@babajat 

Here is a link to download a sample solution file:

IF any of the colums equal any of the value 2022-08-09.pbix

Column = 
INT(
    'Table'[VALUE 1] IN {"XYZ", "1", "3"} 
    || 'Table'[VALUE 2] IN {"XYZ", "1", "3"} 
        || 'Table'[VALUE 3] IN {"XYZ", "1", "3"} 
            || 'Table'[VALUE 4] IN {"XYZ", "1", "3"} 
)

Showcase Report – Contoso By SpartaBI