topic "IN" operator doesn't work in DAX Commands and Tips

"IN" operator doesn't work

Anonymous — Fri, 05 Aug 2016 05:25:24 GMT

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...

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

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

Thanks in advance.

Re: "IN" operator doesn't work

fenixen — Thu, 04 Aug 2016 12:33:19 GMT

I don't know if this part of the forum is actually meant as a help section, but could you provide a bit example data on the issue?

Im wondering if Value 1,2 etc are values inside the column or actual measure values :)

Re: "IN" operator doesn't work

Smruti — Fri, 05 Aug 2016 04:19:35 GMT

Hi,

The SQL functions IN is useful to implement tests over a set of values. This article describes the corresponding syntax in DAX language.

Implementing IN as nested OR conditions

Consider the following query:

1
2
3
SELECT DISTINCT EnglishCountryRegionName
FROM DimGeography
WHERE CountryRegionCode IN ('US', 'CA', 'AU' )

In DAX there are no operators that corresponds to the IN available in SQL. Thus, you have to write a list of corresponding nested OR functions:

1
2
3
4
5
6
7
8
9
10
11
EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    OR (
        OR (
            Geography[Country Region Code] = "US",
            Geography[Country Region Code] = "CA"
        ),
        Geography[Country Region Code] = "AU"
    )
)

As an alternative, you can use the logical OR operator (||):

1
2
3
4
5
6
7
EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    Geography[Country Region Code] = "US"
    || Geography[Country Region Code] = "CA"
    || Geography[Country Region Code] = "AU"
)

This DAX syntax could be a real issue when the list of values to test is long, because the length of the query string might become unmanageable. At that point, a possible alternative is storing the list of values in a separate table, similar to the one called Selection in the following example:

1
2
3
4
5
6
7
8
9
10
11
12
EVALUATE
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    FILTER (
        ALL ( Geography[Country Region Code] ),
        CONTAINS (
            VALUES ( Selection[Country Region Code] ),    
            Selection[Country Region Code],
            Geography[Country Region Code]
        )
    )
)

Re: "IN" operator doesn't work

ibarrau — Tue, 06 Sep 2016 13:50:34 GMT

How about Power Query "M", can I have something similar to "In" operator with it?