Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using the IN operator without explicitely typing out the values

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!

Need Help
  • johnt75's avatar
    johnt75
    4 years ago

    In the getRelevantIDs variable change CALCULATE to CALCULATETABLE

4 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        johnt75
        Super User
        4 years ago

        In the getRelevantIDs variable change CALCULATE to CALCULATETABLE