Creating a new column with multiple IF-statements from different tables

[MarcLykke]

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

[PaulOlding · Accepted solution]

Hi @MarcLykke 

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

[MarcLykke]

Perfect!
Thank you very much 🙂