Using the IN operator without explicitely typing out the values

[Anonymous]

Hello,

While using the IN operator I am encountering an issue because my list of values is calculated (it changes with the x-axis of my visual) and cannot be typed out.

I know how to use IN with the explicitely typed out values, e.g.

table[season] IN {"spring", "summer"}

But is it possible to use it with a row/table such as

table[season] IN [measure]

?

The (simplified) examples of my measures look as follows:

getRelevantIDs =
CALCULATE(
    VALUES(table1[id]),
    FILTER(
        table1,
        -- some filter criteria based on the x-axis of the visual...
    )
)

countFilteredProjects =
CALCULATE(
    DISTINCTCOUNT(table[id]),
    FILTER(
        ALL(table1),
        -- some other filter criteria,
        table1[id] IN [getRelevantIDs]
    )
)

But this doesn't work and I receive the error that "[getRelevantIDs] is not a valid table".

Is there a way to do this? Or an alternative to using IN?

Thank you for your input!

[johnt75]

You can build a temporary table in a variable, and you can use tables as filters, so the below should work I think

countFilteredProjects =
var getRelevantIDs =
CALCULATE(
    VALUES(table1[id]),
    FILTER(
        table1,
        -- some filter criteria based on the x-axis of the visual...
    )
)
CALCULATE(
    DISTINCTCOUNT(table[id]),
    FILTER(
        ALL(table1),
        -- some other filter criteria,
        getRelevantIDs
    )
)

[Anonymous]

Hi johnt75,

Thank you for your suggestion! I have tried to combine the two measures into one and used getRelevantIDs in the filter:

countFilteredProjects =

VAR getRelevantIDs =
CALCULATE(
    VALUES(table1[id]),
    FILTER(
        table1,
        -- some filter criteria based on the x-axis of the visual...
    )
)

RETURN
CALCULATE(
    DISTINCTCOUNT(table1[id]),
    FILTER(
        ALL(table1),
        -- some other filter criteria,
        getRelevantIDs
    )
)

The measure itself doesn't display an error anymore, but whenever I try to use it in a visual I get "MdxScript(Model) (2675, 9) Calculation error in measure 'measures'[countFilteredProjects]: A table of multiple values was supplied where a single value was expected."

I am not sure if we can just use the result of VALUES as a argument for FILTER?

For example, in this post DAX Filter A Table by Another Table the accepted solution is something like

CALCULATE(SUM(table1[Values]), FILTER(Table1, Table1[Product ID] IN VALUES(Table2[Product ID])))

and I don't understand why it doesn't work in my case here. I am using IN VALUES(...), just with the result of VALUES calculated separately.

[johnt75 · Accepted solution]

In the getRelevantIDs variable change CALCULATE to CALCULATETABLE

[Anonymous]

With CALCULATETABLE it works just as expected! So simple and yet I would never have thought of it. Thank you so much 🙂

My finished measure looks now like this:

countFilteredProjects =

VAR getRelevantIDs =
CALCULATETABLE(
    VALUES(table1[id]),
    FILTER(
        table1,
        -- some filter criteria based on the x-axis of the visual...
    )
)

RETURN
CALCULATE(
    DISTINCTCOUNT(table1[id]),
    FILTER(
        ALL(table1),
        -- some other filter criteria &&
        table1[id] IN getRelevantIDs
    )
)

(It only works when having the temporary table in a variable and not if it is in a separate measure. But that is not an issue.)