Forum Discussion
Creating a new column with multiple IF-statements from different tables
Hi everyone
I am trying to create a new column in table 1 in which it should say "Yes" if several conditions from the 3 separate tables are met.
All three tables are related by the ID-variable. It is possible for the same ID to appear in several rows in both table 2 and 3.
The new column should say "Yes" if the status from table 1 = "Active", AND if the colour = "Red" OR "Blue" in table 2, AND if the End date > Today() in table 3.
I have tried several different solutions without any luck. Can anyone help me with this one?
Table 1:
| ID | Status | New column |
| ID 2349 | Active | |
| ID 1234 | Active | |
| ID 9876 | Not active |
Table 2:
| ID | Colour | Brand |
| ID 2349 | Red | Brand 1 |
| ID 1234 | Blue | Brand 2 |
| ID 9876 | Green | Brand 3 |
Table 3:
| ID | Start Date | End date |
| ID 2349 | 10/11/2021 | 22/12/2021 |
| ID 1234 | 05/06/2021 | 20/10/2021 |
| ID 9876 | 07/10/2021 | 10/05/2022 |
Hi Anonymous
Here's some DAX for your new column
New column = IF( Table1[Status] <> "Active", "No", VAR _RedOrBlue = CALCULATE(COUNTROWS(Table2), Table2[Colour] IN {"Red", "Blue"}) VAR _EndAfterToday = CALCULATE(COUNTROWS(Table3), Table3[End date] > TODAY()) VAR _Result = IF(_RedOrBlue >= 1 && _EndAfterToday >= 1, "Yes", "No") RETURN _Result )Assuming your model looks something like this
2 Replies
PaulOldingSolution Sage
Hi Anonymous
Here's some DAX for your new column
New column = IF( Table1[Status] <> "Active", "No", VAR _RedOrBlue = CALCULATE(COUNTROWS(Table2), Table2[Colour] IN {"Red", "Blue"}) VAR _EndAfterToday = CALCULATE(COUNTROWS(Table3), Table3[End date] > TODAY()) VAR _Result = IF(_RedOrBlue >= 1 && _EndAfterToday >= 1, "Yes", "No") RETURN _Result )Assuming your model looks something like this