DAX alternative for IN function

Question

I have a table in SSAS Tabular model having delivery date of vehicles.

There is a dimension table with Age values -

I need to write a DAX to show the count of delivery for each year.

In the PowerBI report which connects to the Tabular model, the user can select the Age (multiple) from the filter which is on Dim_Age. So if the user selects Age 1 it should show the count of 2019 deliveries for the year 2020.

Now the challenge is I am working on SSAS 2016 build version 13.0.5426.0. This version does not support "IN" function. I have tried below and this does not work.

VAR selecteyear =
    IF ( HASONEVALUE ( Dim_Cal[year] ); VALUES ( Dim_cal[year] ) )
CALCULATE (
    COUNT ( Delivery_Data[Vechile] );
    ( selectedyear - Delivery_Data[DeliveryYear] )
        IN VALUES ( Dim_Age[Age] )
)

Please help to know the alternate solution.

Alexis Olson Alexis Olson · Accepted Answer · 2020-04-30T13:58:19

In general, the CONTAINS function is the workaround in older versions that don't support IN.

Here's how you could use it in you situation:

AgedCount = 
VAR selectyear =
    IF ( HASONEVALUE ( Dim_Cal[year] ), VALUES ( Dim_Cal[year] ) )
RETURN
    CALCULATE (
        COUNT ( Delivery_Data[Vechile] ),
        FILTER (
            ALL ( Dim_Cal ),
            CONTAINS ( Dim_Age, Dim_Age[Age], selectyear - Dim_Cal[year] )
        )
    )

This iterates through the whole Dim_Cal table and for each row, checks if selectyear - Dim_Cal[year] is one of the values selected from the Dim_Age[Age] filter.

DAX alternative for IN function

1 Answers