Comparing EnumLists

[Grth]

Hey everyone,

I'm trying to slice a view using a blunt EnumList comparison in the following way:

• Table "Tasks" has an EnumList column [Context] ("Database", "Infrastructure", "Network", etc.)
• Table "Users" has an EnumList column [TaskContext] (same as Tasks[Context])

The idea being that a User can have one or more contexts assigned to them, and the slice on their Tasks view will only show Tasks that share context with them. If any value in Tasks[Context] matches any value in Users[TaskContext] I want to return TRUE (or YES).

My first experiment was seeing if IN() would be smart enough to compare multiple values:

IN([Context], SELECT(Users[TaskContext], ([Email] = USEREMAIL()), FALSE))

Which I didn't really expect to work. On testing, it seemed to flatten the EnumLists and compare them as a string (comma separated values).

So then I tried some more list friendly functions, like the following (Condition removed for brevity):

COUNT(INTERSECT([Context], SELECT(Users[TaskContext], ([Email] = USEREMAIL()), FALSE)))

This time I got an error - "INTERSECT does not accept a list of list." Which is fair enough. I know that my SELECT() returns a single row with the current data, but it obviously returns it as a list of EnumLists with a single row. So then I tried:

COUNT(INTERSECT([Context], TOP(SELECT(Users[TaskContext], ([Email] = USEREMAIL()), FALSE), 1)))

Which threw the same error, because again TOP() returns a list of EnumLists with a single row. Next I went to:

COUNT(INTERSECT([Context], INDEX(SELECT(Users[TaskContext], ([Email] = USEREMAIL()), FALSE), 1)))

I thought this might be a winner because it expects the output to be a singular value. But unfortunately it flattens [TaskContext] and throws "INTERSECT takes two arguments of type list". I get the same error with:

COUNT(INTERSECT([Context], LOOKUP(USEREMAIL(), "Users", "Email", "TaskContext")))

So what am I missing? How can I get the value of Users[TaskContext] without it being a list of lists or a list flattened into a string?

Thanks!

[Joseph_Seddik · Accepted solution]

The Slice row filter expression executes within the context of each row, one by one; so you don't need a table lookup function like SELECT() and similar. Think individual rows.

Thus, first create a current user slice as explained in this excellent post:

Current User (Slice) | How to conform your app a... - Page 3 - Google Cloud Community  

Then your row filter condition could be something like:
  
  NOT( LIST() = INTRESECT( ANY(currentUser[TaskContext]), [Context]) )

[Grth]

Outstanding, I actually have a CurrentUser slice I'm using for a Primary view - it seems really obvious in hindsight to use it this way.

So looking at the row filter, you're comparing the intersection to an empty list and then negating the result? I think I can shortcut that as: 

ISNOTBLANK(INTERSECT([...]))

Which is probably exactly the same under the hood, but reads better to my caveman brain. 

Also, I found that ANY(currentUser[TaskContext]) still flattens to a string value. The link you provided gives a solution though - CONCATENATE() and then SPLIT(). I tried that and it all works! I feel like the CONCATENATE() is redundant though and it seems to work without it?

 Anyway, thanks very much!

[Joseph_Seddik]

Welcome!

@Grth wrote:

ISNOTBLANK(INTERSECT([...]))

---

This is actually better. 

---

@Grth wrote:

CONCATENATE() and then SPLIT()

---

INTERSECT() is better.