Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

@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
Not applicable

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




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors