DAX - Filter Table by Multi Select Filter Condition

Question

Am trying to filter a table with Multi Selection Filter.

DAX: To select the values in the filter selected (Multi)

SelectedEnvironments =
CONCATENATEX (
    VALUES ( Environments[ShortEnvName] ),
    Environments[ShortEnvName],
    ", "
)

Result:

But when trying to filter table based on above filter variable doesnt return anything.

DAX:

Aggregated Usage =
VAR __SelectedEnvironments =
    CONCATENATEX (
        VALUES ( Environments[ShortEnvName] ),
        Environments[ShortEnvName],
        ", "
    )
RETURN
    CALCULATETABLE (
        LastestDBUsage,
        LastestDBUsage[Environment] IN { __SelectedEnvironments }
    )

If I hard code the values within IN operator it work fine. What am doing wrong? Do I need to format the string for IN operator

DAX (Works fine with Hard Code Values)

Aggregated Usage =

VAR __SelectedEnvironments =
    CONCATENATEX (
        VALUES ( Environments[ShortEnvName] ),
        Environments[ShortEnvName],
        ", "
    )
RETURN
    CALCULATETABLE (
        LastestDBUsage,
        LastestDBUsage[Environment] IN { "DEV", "TST" }
    )

sergiom sergiom · Accepted Answer · 2021-01-12T09:02:01

Actually, the IN operator works on tables, CONCATENATEX returns a string.

{ __SelectedEnvironments }

returns a table with one row consisting of one column like for instance "DEV, TST"

to make the code work it would be changed to use a table instead, like for instance

Aggregated Usage =
VAR __SelectedEnvironments = VALUES ( Environments[ShortEnvName] )
RETURN
    CALCULATETABLE (
        LastestDBUsage,
        LastestDBUsage[Environment] IN __SelectedEnvironments
    )

DAX - Filter Table by Multi Select Filter Condition

1 Answers