Difference between INDEX() and ANY() with lists

[MultiTech]

By and large, you can use INDEX() or ANY() interchangeably.

---

There is 1 scenario that I know of where a false-positive can be reached when using ANY()

Let’s say you’ve got a Current_User (slice) with a field that users can change to turn on a “special mode” inside your app, and you’ve keyed everything (show ifs for columns, views, actions, etc.) off the presence of a value inside that field.

If you used the following formula:

ISNOTBLANK(ANY(Current_User[User_Special_Mode_Flag]))

When this is evaluated, and the list is empty, it can return TRUE.

This happens because the ANY() tells the system to pull out the first item in the list

• that list being all the records inside the Current_User (slice).

And since there is a record present for the Current_User (slice)…

• ISNOTBLANK() = true
  • even though the actual value is {nothing}

---

---

If you use INDEX() however, this doesn’t happen.

ISNOTBLANK(INDEX(Current_User[User_Special_Mode_Flag], 1))

• This is because INDEX() tells the system to extract the first value from the list… not just take the first item from the list.
  • this is slightly different from ANY() in the sense that INDEX() both takes the first item, and… um… extracts (??) it too. lol

So the final result is not the first entry, it’s the first value.

(I hope that made some sort of sense)

---

Additionally, ANY() has sometimes been known to change data-types from whatever it was (number, decimal, list) to a text.

• INDEX() has always maintained the correct data-type

[Marc_Dillon]

Interesting.

However, I was unable to replicate your scenario:

MultiTech_Visions:

and the list is empty

I thought maybe I mis-read, so I changed this, to have a Slice of zero records:

But still:

[MultiTech]

Awesome!

• this is one of those wishy-washy bugs, it gets fixed then it breaks then it gets fixed again, but it seems like lately that’s starting to taper off and it’s just always starting to work.

I don’t remember if there’s any more edge cases, the only one I could remember is this is not blank thing.

• but thankfully it looks like that’s no longer a problem!

[Ami]

If i recall correctly, i have used ANY() where i need to look back at a parent table from a child record in order to change the value of a parent, what you said makes sense now when i look back. So Index is now my goto expression? I would think that after so many years there would be something built in that would do that.