Calculated column and values in different rows/ get rid of row context

[Applicable88]

Hello,

I want to have a calculated column. My sample data looks like this:

Department	Guards	Helpers
Security
Security	Enough
Security		not sufficient
Security
Warehouse	Enough
Warehouse		sufficient

I already filtered the department to "security". In the background is a calculation going on to tell me if for a specific day I have enough guards and helpers or not. Unfortunately its not written in every row. And the string values of guards and value are also in different rows. 

I want another column like this: 

Department	Guards	Helpers	Enough overall?
Security			No
Security	Enough		No
Security		not sufficient	No
Security			No
Warehouse	Enough		Yes
Warehouse		sufficient	Yes

I tried following function for calculated column :

if ( [Department]= "Security" && [Guards] = "Enough" &&  [Helpers] = "sufficient", "YES",

if ( [Department]= "Security" && [Guards] = "Enough" &&  [Helpers] = " not sufficient", "No",....... (more conditions for others departments and conditions)

How do I get the right values like "Yes" or "No" into every row and also correctly for every specific allocated department?

Thank you very much in advance. 

Best. 

[v-yetao1-msft]

Hi @Applicable88 

I have two situations that need to be confirmed.

(1)If [Department]= "Security" , then [Guards] = "Enough" and [Helpers] = "sufficient" must be satisfied at the same time, then return “Yes” ?

(2)If [Department]<> "Security" , then [Guards] = "Enough" and [Helpers] = "sufficient" only need to satisfy one of them, then return “Yes” ?

If the conditions meet the two situations I described above, please refer the calculated column that I provide .

Judgment = SWITCH(TRUE(),'Table'[Department]="Security" && 'Table'[Guards]="Enough" && 'Table'[Helpers]="sufficient","Yes",
                         'Table'[Department]<>"Security" && 'Table'[Guards]="Enough" || 'Table'[Helpers]="sufficient","Yes","No")     

And the result is as shown :

Best Regards

Community Support Team _ Ailsa Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Applicable88]

Hello @v-yetao1-msft ,

thank you for your effort. Both guards must have enough and helpers must have sufficient to be "YES"

If one of them are either "not Enough" or not sufficient then "No".

I should have created alonger sample table to make it clear, to cover all cases. To make it less confusing I only use two states , which is "sufficient" and "not sufficient":

Department	Guards	Helpers	Flag
Security			No
Security	sufficient		No
Security		not sufficient	No
Security			No
Warehouse	sufficient		Yes
Warehouse		sufficient	Yes
Subsidiary			No
Subsidiary	not sufficient		No
Subsidiary			No
Subsidiary		sufficient	No
Subsidiary			No
Subsidiary			No

I there a way to do it when both string values are not on the same row?
As long there is one "not sufficient", its definitely a "No". Only two "sufficient" for the specific department returns a "Yes". In your example the Warehouse department has both string values on the same row, thats why your switch() functions works. But in my datamodel they are mostly on different rows.

Thank you very much in advance.

Best.