Forum Discussion
Solved
Create conditional column based on Two tables
I have two tables: Tests and Results. In the data model, the two tables are connected by the key column and it is a one (Tests) to many (Results). relationship.
Tests:
| key | Lot code |
| 1 | xxx |
| 2 | yyy |
| 3 | zzz |
Results :
| key | test | condition |
| 1 | A | pass |
| 1 | B | pass |
| 2 | A | fail |
| 2 | D | pass |
| 2 | C | pass |
| 3 | B | fail |
| 3 | D | fail |
I want to produce the following table without merging the two tables:
| Lot code | Final Condition |
| xxx | pass |
| yyy | fail |
| zzz | fail |
The final table is produced by first getting the lot code and condition columns from the two tables. Then, If the lot code has any condition = fail, then the final condition of the lot will fail. In other words, for a lot to pass, the lot can only have pass conditions.
| Lot code | condition | final condition |
| xxx | pass | pass |
| xxx | pass | pass |
| yyy | fail | fail |
| yyy | pass | fail |
| yyy | pass | fail |
| zzz | fail | fail |
| zzz | fail | fail |
I'm thinking of creating a new measure or new column on Power BI Desktop. But totally clueless how to write this. I hope I explained everything clearly. Please let me know if I need to clarify anything!
Thank you in advance!
