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



Jaywant-Thorat's avatar
Jaywant-Thorat
Icon for Super User rankSuper User
9 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
Icon for Helper II rankHelper II
9 months ago

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