From SQL to DAX: IN and EXISTS

Implementing IN as nested OR conditions

Consider the following query:

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

In DAX you can use a similar syntax if the IN operator is available (it was introduced in 2016):

EVALUATE 
CALCULATETABLE (
    VALUES ( Geography[Country Region Name] ),
    Geography[Country Region Code] IN { "US", 'CA', 'AU' }
)

Before 2016, it was necessary to write a list of corresponding nested OR functions:

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 to both the previous syntaxes, you can use the logical OR operator (||):

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. Therefore, the IN operator is usually better.
Without the IN operator, a possible alternative was storing the list of values in a separate table, similar to the one called Selection in the following example:

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]
        )
    )
)

Implementing EXISTS in DAX

The EXISTS function in SQL is important to efficiently test whether at least one row exists in a correlated subquery. For example, consider the following SQL code:

SELECT DISTINCT
        ModelName
FROM    DimProduct p
WHERE   EXISTS ( SELECT NULL
                 FROM   FactInternetSales s
                 WHERE  s.ProductKey = p.ProductKey )
ORDER BY ModelName

Assuming that a relationship exists between Internet Sales and Product tables, in DAX you can write a first version using COUNTROWS:

EVALUATE 
FILTER (
    VALUES ( Product[Model Name] ),
    CALCULATE ( COUNTROWS ( 'Internet Sales' ) ) > 0
)
ORDER BY Product[Model Name]

However, using COUNTROWS is the slower technique, because it forces to count the exact number of rows satisfying the condition. A better alternative is using the ISEMPTY function, which is semantically the opposite of EXISTS, so it has to be wrapped within NOT function.

EVALUATE 
FILTER (
    VALUES ( Product[Model Name] ),
    NOT ISEMPTY ( CALCULATETABLE ( 'Internet Sales' ) )
)
ORDER BY Product[Model Name]

Instead of using CALCULATETABLE, in this case you can use a more descriptive RELATEDTABLE function, which has the same behavior and performance, but it is easier to read.

EVALUATE 
FILTER (
    VALUES ( Product[Model Name] ),
    NOT ISEMPTY ( RELATEDTABLE ( 'Internet Sales' ) )
)
ORDER BY Product[Model Name]

Please, note that ISEMPTY is a DAX function introduced in SQL Server 2012 SP1 CU4, so it is available in new version of Power Pivot for Excel 2010 and in Analysis Services builds greater than or equal to 11.0.3368. If you use an older version, or you use Excel 2013, instead of ISEMPTY you can use the following alternative approach based on CONTAINS:

EVALUATE 
FILTER (
    VALUES ( Product[Model Name] ),
    CONTAINS ( 
        RELATEDTABLE ( 'Internet Sales' ), 
        Product[Model Name], 
        Product[Model Name]
    )
)
ORDER BY Product[Model Name]

You should not make too many assumptions about the performance. Query plans might vary depending on the version of the DAX engine you use. It is suggested to analyze the query plans of different alternatives in order to find the best DAX syntax, depending on your volume and distribution of data.