Summarize data based on conditions

[prasadhebbar315]

Hello,

I need help in creating a Result column from the below table based on the condition that both Col1 and Col2 should atleast contain Yes for a Serial_Number. If yes, then the entire Serial_Number should have Yes in Result column.

Serial Number 200 and 400 contains "Yes" atleast once in both Col1 and Col2 , therefore it has "Yes" in Result.

Serial_Number	Col1	Col2	Result
100	Yes	No	No
100	No	No	No
100	No	Yes	No
200	Yes	Yes	Yes
200	No	No	Yes
300	No	Yes	No
300	Yes	No	No
300	No	No	No
400	No	No	Yes
400	Yes	No	Yes
400	Yes	Yes	Yes

[wdx223_Daniel · Accepted solution]

Result=IF(COUNTROWS(FILTER(Table,Table[Serial_Number]=EARLIER(Table[Serial_Number])&&Table[Col1]="Yes"&&Table[Col2]="Yes"))>0,"Yes","NO")

[prasadhebbar315 · Accepted solution]

@wdx223_Daniel Thanks a ton. Your solution has worked.

[MohTawfik]

@prasadhebbar315 

If you want to create this calculation in the table, go to data view, click on new column and type the below formula:

Result =

 IF( 'Table'[Col1] = "Yes" || 'Table'[Col2] = "Yes",

 "Yes",

 "No")

If you want to do this in the report as a measure, go to report view and add new measure as follow:

Result measure =

Var COl1Value =

SELECTEDVALUE('Table'[Col1])

Var Col2value =

SELECTEDVALUE('Table'[Col2])

Return

 IF( COl1Value = "Yes" || Col2value = "Yes",

 "Yes",

 "No")

[prasadhebbar315]

@MohTawfik Thanks for providing the solution

[wdx223_Daniel · Accepted solution]

Result=IF(COUNTROWS(FILTER(Table,Table[Serial_Number]=EARLIER(Table[Serial_Number])&&Table[Col1]="Yes"&&Table[Col2]="Yes"))>0,"Yes","NO")