Forum Discussion
Summarize data based on conditions
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 |
Result=IF(COUNTROWS(FILTER(Table,Table[Serial_Number]=EARLIER(Table[Serial_Number])&&Table[Col1]="Yes"&&Table[Col2]="Yes"))>0,"Yes","NO")
wdx223_Daniel Thanks a ton. Your solution has worked.
4 Replies
Result=IF(COUNTROWS(FILTER(Table,Table[Serial_Number]=EARLIER(Table[Serial_Number])&&Table[Col1]="Yes"&&Table[Col2]="Yes"))>0,"Yes","NO")
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])ReturnIF( COl1Value = "Yes" || Col2value = "Yes","Yes","No")MohTawfik Thanks for providing the solution
wdx223_Daniel Thanks a ton. Your solution has worked.
