Suppose you have a table. Let’s call it Table1. Suppose Table1 only has one row.
In that table there is a column of type List. Let’s call it Column1.
Now you want to create a slice (let’s call it Slice2) of a different table with many rows (let’s call it Table2) based on whether a value (e.g a column, Value2) in a given row is in the list in Column1 of the only row of Table1.
So the filter expression defining Slice2 would be
IN( [Value2], ANY(Table1[Column1]) )
Table1[Column1] is a list of the lists from each row. ANY(Table1[Column1] ) normally pulls a single value from a list. In this case you would expect one list in return, i.e. the [Column 1] value from a single row.
However, if there is only one row in Table1[Column1], what you will actually get is only one item from the list, i.e. one single value back from ANY(Table1[Column1]) ) to compare against. For example, if the value of Table1, Column1 for row 1 is {A, B, C, D}, ANY() will return only A instead of {A, B, C, D}.
This happens only when filtering a slice (as far as I can tell) and is inconsistent with how ANY() behaves in other places. If you apply the exact same formula in a virtual column, the expression works as expected against the whole list. It will also correctly return the full list in a case like this:
ANY(Table1[Column1]) )
There’s something else odd as well: the full list of values is correctly returned if you put something like this in the filter expression for the slice.
IN( [Value2], Table1[Column1] )
But if you put this in a virtual column, it will (expectedly) cause problems, as you are essentially asking to compare a single value to a list of lists.
LinkedIn
Twitter
