cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Applicable88
Post Prodigy
Post Prodigy

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

Hello,

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

 

 

DepartmentGuardsHelpers
Security  
SecurityEnough 
Security not sufficient
Security  
WarehouseEnough 
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: 

 

DepartmentGuardsHelpersEnough overall?
Security  No
SecurityEnough No
Security not sufficientNo
Security  No
WarehouseEnough Yes
Warehouse sufficientYes


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. 

2 REPLIES 2
Ailsa-msft
Community Support
Community Support

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 :

Ailsamsft_0-1626925369849.png

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.

Hello @Ailsa-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":

 

DepartmentGuardsHelpersFlag
Security  No
Securitysufficient No
Security not sufficientNo
Security  No
Warehousesufficient Yes 
Warehouse sufficientYes 
Subsidiary  No
Subsidiarynot sufficient No
Subsidiary  No
Subsidiary sufficientNo
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. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!