'IN' Operator in 'New Measure'

[Anonymous]

HI,

How to write following code in dax Measure ?

if ( Column1 in ("Value1","Value2"...."ValueN") then [Revenue] else 0)

I am struggling here: I am able to give it like this

if ( Column1 ="Value1" or Column1 ="Value2".... or Column1 ="ValueN") then [Revenue] else 0)

which is not efficient...

is there any function that i can use... instead of 'IN' operator...??

I need  calculate( sum(FactT[revenue]), filter(FactT,FactT[Column1]  in ("Value1","Value2"...."Value3")))

I am able to do like this:  calculate( sum(FactT[revenue]), filter(FactT,FactT[Column1] = "Value1" || FactT[Column1] = "Value2"... || FactT[Column1] = "Value1")) 

Thanks in advance.

[radpir · Accepted solution]

hi,

you would need to put the values you want in the IN predicates into a separate table i.e. PREDICATES.

here's the sample setup that i created to illustrate this case:

FactT			PREDICATES
Category	Amount		Column1Values_IN
Value1	100		Value1
Value1	50		Value2
Value2	10		Value3
Value2	20
Value2	30
Value3	10
Value4	40
Value3	90
Value4	1000
Value4	500
Value5	432
Value6	593
Value6	876

and then you could use this formula:

 TotalAmt_IN =

               CALCULATE ( SUM('FactT'[Revenue]),

                       FILTER ( 'FactT', CONTAINS(PREDICATES, PREDICATES[Column1Values_IN], 'FactT'[Column1] ) )

                 )

the formula returns 310 based on the above data.

hope this helps,

radpir