Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Use ID to filter multiple columns

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".

IDTrade typeRealised TodayExpirey Date
1New TradeY01/11/201901/10/2020
1Full unwindY01/11/201902/10/2020
2New TradeN01/11/201905/09/2020
3New TradeY01/11/201915/10/2018
4New TradeY01/11/201905/10/2020
4Full unwindY01/11/201906/10/2020
5New TradeN01/11/201907/07/2020
6New TradeY01/11/201918/11/2020
6Full unwindY01/11/201918/11/2020
7New TradeN01/11/201910/10/2021
8New TradeN01/11/201911/08/2020
9New TradeN01/11/201917/05/2020
10New TradeY01/11/201913/10/2019

 

How would I code this in Dax?

Many thanks in advance

  • Anonymous's avatar
    Anonymous
    6 years ago

    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")

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago
      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.