Flag with Yes or No Based on two columns string, which is not on the same row

[Applicable88]

Hello,

I need a Column Flag base on two other columns, telling me if there is a condition met. My sample table:

Department	Guards	Helpers
Security
Security	sufficient
Security		not sufficient
Security
Warehouse	sufficient
Warehouse		sufficient
Subsidiary
Subsidiary	not sufficient
Subsidiary
Subsidiary		sufficient
Subsidiary
Subsidiary

First column is the department. If the columns "guards" and "helpers" got sufficient then a fourth column should say "YES". My problem is that sufficient and not sufficient created from the database are not on the same row. So the row context with if - statement failed.

The Flag I'm looking for a calculated column:

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

Hope someone has an idea.

Thank you very much in advance.

Best reagards. 

[V-lianl-msft · Accepted solution]

Please try:

Column = 
var guards_ = CALCULATE(DISTINCTCOUNT('Table'[Department]),FILTER('Table',EARLIER('Table'[Department])='Table'[Department]&&'Table'[Guards]="sufficient"))
var helpers = CALCULATE(DISTINCTCOUNT('Table'[Department]),FILTER('Table',EARLIER('Table'[Department])='Table'[Department]&&'Table'[Helpers]="sufficient"))
var count_ = guards_+helpers
return IF(count_=2,"Yes","No"
)

[amitchandak]

@Applicable88 , Create a new column like

new column =
var _cnt_ns = countx(filter(Table, [Department] = earlier([Department]) && [Guards] = "not sufficient" || [Helpers] = "not sufficient"), [Department])
var _cnt_s = countx(filter(Table, [Department] = earlier([Department]) && [Guards] = "not sufficient" || [Helpers] = "sufficient"), [Department])
return
if(not(isblank(_cnt_s)) && isblank(_cnt_ns) , "Yes", "No")

[Applicable88]

@amitchandak , it shows me "No" for every case, even the "Warehouse" department meet the criteria.

[V-lianl-msft · Accepted solution]

Please try:

Column = 
var guards_ = CALCULATE(DISTINCTCOUNT('Table'[Department]),FILTER('Table',EARLIER('Table'[Department])='Table'[Department]&&'Table'[Guards]="sufficient"))
var helpers = CALCULATE(DISTINCTCOUNT('Table'[Department]),FILTER('Table',EARLIER('Table'[Department])='Table'[Department]&&'Table'[Helpers]="sufficient"))
var count_ = guards_+helpers
return IF(count_=2,"Yes","No"
)

[Applicable88]

@V-lianl-msft wow... I think thats it. Can you explain to me whats happening in the Filter part of your function? I read about the earlier() function, but I don't understand what it is actually doing. 

And will this formula also works when "sufficient" of Helpers always comes first and the "sufficient" of Guards comes rows below it? Does the earlier function has an impact on this? And also why there is a ealier function but not like an "later()" function availabe. For examle SQL has lead and lag. 

Thank you very much. 

Best. 

[V-lianl-msft · Accepted solution]

EARLIER function was a way to manipulate the context of a calculation within a particular formula.

The EARLIER function allows us to jump from one context to a prior row context.

In this formula, the EARLIER function is used as the condition of distinctcount of other guards... from the same Department against each guards... .