More Efficient way of doing this?

[NCD]

I think I'm doing something wrong, and I'm afraid it might hurt me in the long run so if anybody could teach me a more efficient way to do this please help me. Thank you in advance.

1) I have a Security filter set on GChild Table;

= IF([Status] = "Canceled", False, True)

3) Not in above image, but I also have a Current_User Table that stores ; id, userrole, username

2) I've created a Slice out of Parent Table, Row filter condition ;

= IFS(

COUNT(

SELECT( GChild[Status], ( [child_id].[parent_id].[id] = [_THISROW].[id] ), FALSE)

) = 0, FALSE,

INDEX(Current_User[id], 1) = [user_id], TRUE,

INDEX(Current_User[userrole], 1) = "admin", TRUE

)

It works successfully, and as a result I do not see the data circled in dotted red (in above image), but I am able to see data circled in dotted blue - the canceled record (in above image).

Like I’ve said it works fine at the moment, but I’m sure there’s a better solution for this?

[1minManager · Accepted solution]

The security filter just needs to be [Status] = "Canceled", other than that it looks ok.  But what I'd suggest is creating a temporary table view of your security filter just to confirm its working.

[1minManager · Accepted solution]

The security filter just needs to be [Status] = "Canceled", other than that it looks ok.  But what I'd suggest is creating a temporary table view of your security filter just to confirm its working.

[Aurelien · Accepted solution]

Hi @NCD 

---

@NCD wrote:

1) I have a Security filter set on GChild Table;

= IF([Status] = "Canceled", False, True)

---

You may want to try instead:

NOT([Status]="Canceled")

or

[Status]<>"Canceled"

it could be easier and more efficient.

---

@NCD wrote:

2) I've created a Slice out of Parent Table, Row filter condition ;

= IFS(

COUNT(

SELECT( GChild[Status], ( [child_id].[parent_id].[id] = [_THISROW].[id] ), FALSE)

) = 0, FALSE,

INDEX(Current_User[id], 1) = [user_id], TRUE,

INDEX(Current_User[userrole], 1) = "admin", TRUE

)

---

You may want to simplify this part:

IFS(
  COUNT(
    SELECT( GChild[Status], ( [child_id].[parent_id].[id] = [_THISROW].[id] ), FALSE)
  ) = 0, FALSE,
  INDEX(Current_User[id], 1) = [user_id], TRUE,
  INDEX(Current_User[userrole], 1) = "admin", TRUE
)

with that (no "[.id]" part):

IFS(
  COUNT(
    SELECT( GChild[Status], ( [child_id].[parent_id] = [_THISROW] ), FALSE)
  ) = 0, FALSE,
  INDEX(Current_User[id], 1) = [user_id], TRUE,
  INDEX(Current_User[userrole], 1) = "admin", TRUE
)

If you don't care about sequentiality of conditions (IFS part), you may want to try:

OR(
  COUNT(SELECT( GChild[Status], ( [child_id].[parent_id] = [_THISROW] ), FALSE)) <> 0, 
  INDEX(Current_User[id], 1) = [user_id],
  INDEX(Current_User[userrole], 1) = "admin"
)

which, eventually, is equivalent to:

OR(
  ISNOTBLANK(SELECT( GChild[Status], ( [child_id].[parent_id] = [_THISROW] ), FALSE)),
  INDEX(Current_User[id], 1) = [user_id],
  INDEX(Current_User[userrole], 1) = "admin"
)

 or even more simple:

OR(
  ISNOTBLANK(FILTER("GChild",[child_id].[parent_id] = [_THISROW])),
  INDEX(Current_User[id], 1) = [user_id],
  INDEX(Current_User[userrole], 1) = "admin"
)