Forum Discussion

TRADER083's avatar
TRADER083
Helper II
8 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, but not if I wrap them in the IF statement.

 

Do I need to convert final_list to a table for this to work?

 

VAR list1 = {"123", "456"}
VAR list2 = {"789"}
VAR final_list = IF(condition = true(), list1, list2)

RETURN FILTER (table, column IN final_list) <- final_list is not a valid table

Thank you,

Kevin

Need Help
  • ChielFaber's avatar
    ChielFaber
    8 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



6 Replies

  • ChielFaber's avatar
    ChielFaber
    Super User
    8 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



  • Jaywant-Thorat's avatar
    Jaywant-Thorat
    Super User
    8 months ago

    Why your DAX doesn't work

    In DAX:

    • { "123", "456" } is an inline row constructor, not a table.
    • list1 and list2 work in IN only when used directly, because DAX expands them inline.
    • When you wrap them inside an IF, the return type becomes variant, and DAX no longer treats it as a table.

    So yes — you must convert the list into a table before using it inside IN.

    Correct DAX

    Convert the list into a table using DATATABLE or SELECTCOLUMNS.

    ----------DAX----------
    VAR list1 = SELECTCOLUMNS({ "123", "456" }, "Value", [Value])
    VAR list2 = SELECTCOLUMNS({ "789" }, "Value", [Value])
    VAR final_list = IF(condition = TRUE(), list1, list2)

    RETURN
    FILTER(
    table,
    table[column] IN final_list
    )

    ----------DAX----------
    Now final_list is a valid single-column table, and IN works perfectly.

     

    ==================================================

    Did I answer your question? Mark my post as a solution! This will help others on the forum!

    Appreciate your Kudos!!

    Jaywant Thorat | MCT | Data Analytics Coach
    Linkedin: https://www.linkedin.com/in/jaywantthorat/


    Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

    #MissionPowerBIBharat
    LIVE with Jaywant Thorat from 15 Dec 2025
    8 Days | 8 Sessions | 1 hr daily | 100% Free

    • TRADER083's avatar
      TRADER083
      Helper II
      8 months ago

      Hi Jaywant-Thorat, unfortunately, it has the same error. Looks like the IF statement is still converting the result to variant.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 months ago

    Hi TRADER083 ,

    Try below code.

     

    VAR FinalList =

        IF(

            condition,

            ROW("Value","123") & ROW("Value","456"),

            ROW("Value","789")

        )

     

    RETURN

    FILTER(table, table[column] IN FinalList

    [Value])

     

    If my response as resolved your issue please mark it as solution and give kudos.