Solved: AND() formula

Yokuinin

I am trying use "Ref" for input.

I would like to control the data with "Valid if".

My app shows an error as below.

Here is my data.

-table "staff"

I would like to "ref" both of Place and Time are True.

Here is my formula,

AND(
FILTER("staff", [Place]=TRUE),
FILTER("staff", [Time]=TRUE)
)

This is error massage.

Condition AND(SELECT(staff[IDstaff],([Place] = "TRUE")), SELECT(staff[IDstaff],([Time] = "TRUE"))) has an invalid structure: subexpressions must be Yes/No conditions

lynchk21

You probably would be best off using a slice on the "staff" table with the expression AND([Place]=TRUE,[Time]=TRUE) and then use the slice as your ref table.

You could also use a SELECT expression in suggested values to only show Names in which time and place are true:

SELECT(staff[name],AND([Place]=TRUE,[Time]=TRUE))

View solution in original post

WillowMobileSys

By "control the data with "Valid if" " I assume you mean to set the list of possible choices.

AND() is a boolean operator that returns Yes or No. It doesn't join things together. However, you can join two lists together using the "+" operator. Change your expression to this:


FILTER("staff", [Place]=TRUE)
+
FILTER("staff", [Time]=TRUE)

Yokuinin

Thank you for your reply.

I tried to use "+" to combine 2 factors.
Unfortunately it seems doesn't work well.

Using "+", Emily and David also appears to be picked up.

lynchk21

You probably would be best off using a slice on the "staff" table with the expression AND([Place]=TRUE,[Time]=TRUE) and then use the slice as your ref table.

You could also use a SELECT expression in suggested values to only show Names in which time and place are true:

SELECT(staff[name],AND([Place]=TRUE,[Time]=TRUE))

Yokuinin

Thank you for your advice!
I can make it with SELECT expression.
For my understanding, I would like to pick up the "selected" value when "Ref" use.

I still watch all data on the "plain" table view (without slice).
In my case, "slice" can be used?

lynchk21

Sorry I don't quite understand "For my understanding, I would like to pick up the "selected" value when "Ref" use."

As for the slice, if you need to display all values, then the SELECT() option seems better

WillowMobileSys

Why didn't it work? Please show us.

Yokuinin

I set the "+" in the "Valid if",

then all data can be picked up on "Ref" column.