Use ID to filter multiple columns

[Anonymous]

Hello,

I'm looking to create the realized column shown in the table below. It works off the following pseudo-code:  

If "Trade Type" = "New Trade" and there is no "Full Unwind" for this ID and "Expiry Date" >= Today(), then "N".
Otherwise, "Y".

ID	Trade type	Realised	Today	Expirey Date
1	New Trade	Y	01/11/2019	01/10/2020
1	Full unwind	Y	01/11/2019	02/10/2020
2	New Trade	N	01/11/2019	05/09/2020
3	New Trade	Y	01/11/2019	15/10/2018
4	New Trade	Y	01/11/2019	05/10/2020
4	Full unwind	Y	01/11/2019	06/10/2020
5	New Trade	N	01/11/2019	07/07/2020
6	New Trade	Y	01/11/2019	18/11/2020
6	Full unwind	Y	01/11/2019	18/11/2020
7	New Trade	N	01/11/2019	10/10/2021
8	New Trade	N	01/11/2019	11/08/2020
9	New Trade	N	01/11/2019	17/05/2020
10	New Trade	Y	01/11/2019	13/10/2019

How would I code this in Dax?

Many thanks in advance

[Anonymous · Accepted solution]

@Anonymous 

Please create a calculated column as per below code

Column = 
VAR tt = CALCULATE(CONCATENATEX(Test,Test[Trade type]," "),ALLEXCEPT(Test,Test[ID]))
RETURN IF(AND(tt="New Trade",Test[Expirey Date]>=Test[Today]),"N","Y")

[Anonymous]

@Anonymous 

Many thanks for your contribution! The answer is correct however this will only work on import and not direct query for those who read and also wish to follow this technique.