Forum Discussion

TRADER083's avatar
TRADER083
Icon for Helper II rankHelper II
9 months ago
Solved

Using a string as parameter within IN operator

Hi everyone,   I want to use a list as a filter based on a condition, however the following code indicates that final_list is not a valid table. The code will work if I use list1 or list2 directly,...
Need Help
  • ChielFaber's avatar
    ChielFaber
    9 months ago

    I tried recreating your problem by making a test table:

    TestInputTable =
    DATATABLE(
        "Code", STRING,
        "DummyValue", INTEGER,
        {
            { "123", 10 },
            { "456", 20 },
            { "789", 30 }
        }
    )


    After this I tried with dax to create a new calculated table that filters the original table based on a condition. I don't know which condition your using so I made a true/false condition in the dax code that could easily be switched out.

    I made the table as follows:

    Filtered TestInputTable =
    VAR list1 = { "123", "456" }
    VAR list2 = { "789" }
    VAR UseList1 = TRUE()   -- of jouw echte conditie

    RETURN
    FILTER (
        'TestInputTable',
        IF (
            UseList1,
            'TestInputTable'[Code] IN list1,
            'TestInputTable'[Code] IN list2
        )
    )
     
    This results in a filtered calculated table:

    and when condition is FALSE:


    Hope this is helpfull



ChielFaber's avatar
ChielFaber
Icon for Super User rankSuper User
9 months ago

I tried recreating your problem by making a test table:

TestInputTable =
DATATABLE(
    "Code", STRING,
    "DummyValue", INTEGER,
    {
        { "123", 10 },
        { "456", 20 },
        { "789", 30 }
    }
)


After this I tried with dax to create a new calculated table that filters the original table based on a condition. I don't know which condition your using so I made a true/false condition in the dax code that could easily be switched out.

I made the table as follows:

Filtered TestInputTable =
VAR list1 = { "123", "456" }
VAR list2 = { "789" }
VAR UseList1 = TRUE()   -- of jouw echte conditie

RETURN
FILTER (
    'TestInputTable',
    IF (
        UseList1,
        'TestInputTable'[Code] IN list1,
        'TestInputTable'[Code] IN list2
    )
)
 
This results in a filtered calculated table:

and when condition is FALSE:


Hope this is helpfull



TRADER083's avatar
TRADER083
Icon for Helper II rankHelper II
9 months ago

Thank you ChielFaber, works as expected!