CODEWITHSUNDEEP
powerbidax
Sreedhar
Sreedhar

Reputation: 30045

DAX - Filter Table by Multi Select Filter Condition

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:

enter image description here

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" }
    )

Upvotes: 0

Views: 880

Answers (1)

sergiom
sergiom

Reputation: 4887

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
    )

Upvotes: 1

Related Questions