Forum Discussion
'IN' Operator in 'New Measure'
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.
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
1 Reply
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