topic Creating a new column with multiple IF-statements from different tables in DAX Commands and Tips

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

Anonymous — Tue, 23 Nov 2021 16:08:14 GMT

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

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

PaulOlding — Tue, 23 Nov 2021 16:47:30 GMT

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

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

Anonymous — Thu, 25 Nov 2021 08:04:16 GMT

Perfect!
Thank you very much 🙂