Forum Discussion
Custom Column with Ifs/And statement
Hi - I'm new to BI and trying to figure out the best way to create the below custom column in an existing report.
Looking to return 'accept' in the new column if each Col A/B/C contain any of the values provided in Col1/2/3, respectively. All rows not meeting this criteria would simply return 'other' in the new column.
So for each row, If ColA contains one of the values provided in Col1, AND ColB contains one of the values provided in Col2, AND ColC contains one of the values provided in Col3, then Return 'accept' in the custom colum
thank you!
| Report 1 | Report 2 | ||||||
| Col A | Col B | Col C | Custom Col | Col 1 | Col 2 | Col 3 | |
| 5 | orange | x | Pass | 1 | apple | x | |
| 2 | apple | y | Accept | 2 | banana | y | |
| 19 | kiwi | z | Pass | 3 | kiwi | z | |
| 5 | banana | y | Accept | 4 | |||
| 4 | apple | z | Accept | 5 | |||
| 1 | kiwi | o | Pass | ||||
| 3 | grape | p | Pass | ||||
| 2 | grape | y | Accept |
Hi,
Please try this measure:
Measure = IF(MAX(Report1[Col A]) in FILTERS(Report2[Col 1])&&MAX(Report1[Col B]) in FILTERS(Report2[Col 2])&&MAX(Report1[Col C]) in FILTERS(Report2[Col 3]),"Accept","Pass")And the result shows:
Hope this helps.
Best Regards,
Giotto Zhi
1 Reply
Hi,
Please try this measure:
Measure = IF(MAX(Report1[Col A]) in FILTERS(Report2[Col 1])&&MAX(Report1[Col B]) in FILTERS(Report2[Col 2])&&MAX(Report1[Col C]) in FILTERS(Report2[Col 3]),"Accept","Pass")And the result shows:
Hope this helps.
Best Regards,
Giotto Zhi