Forum Discussion
Solved
Need help in calculation
Hi everyone,
I m trying to calculate the Final Output column
1. If the same ID has multiple only COW values, print COW
2. If the same ID has multiple only Revisit values, print Revisit
3. If the same ID has a combination of COW and Revisit print Revisit
| ID | Values | Final Output |
| S000947-A-003 | COW | Revisit |
| S000947-A-003 | Revisit | Revisit |
| S000960-A-003 | COW | COW |
| S000960-A-003 | COW | COW |
| S000982-A-003 | Revisit | Revisit |
| S000982-A-003 | Revisit | Revisit |
| S001901-A-001 | Revisit | Revisit |
| S001901-A-001 | COW | Revisit |
| S001901-A-001 | COW | Revisit |
| S001901-A-001 | Revisit | Revisit |
12 Replies
There are probably a few different ways this can be done. Here is my suggestion:
FinalOutput = VAR _ID = [ID] VAR _RowCount = CALCULATE( COUNTROWS( Shree ), FILTER( YourTable, YourTable[ID] = _ID )) VAR _COWCount = CALCULATE( COUNTROWS( Shree ), FILTER( YourTable, YourTable[ID] = _ID && [Values] = "COW" )) + 0 RETURN IF( _RowCount = _COWCount, "COW", "Revisit" )Basically I am calculating all rows for each ID. Then calculating COW Rows for each ID. If they are the same, then "COW", else "Revisit"
IDValuesFinalOutput
S000947-A-003 COW Revisit S000947-A-003 Revisit Revisit S000960-A-003 COW COW S000960-A-003 COW COW S000982-A-003 Revisit Revisit S000982-A-003 Revisit Revisit S001901-A-001 Revisit Revisit S001901-A-001 COW Revisit S001901-A-001 COW Revisit S001901-A-001 Revisit Revisit
Hope this gets you what you are looking for.
Regards,