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
Jano
Helper III
Helper III

DAX: Flag order if timestamp is in between 2 dates and product_id matches

Hey guys,

 

I want to provide some insight on performance of our deals section, so I got data looking like this:

 

Table A - order positions

order_idtimestampproduct_id
12301.01.2021123456789
45604.01.2021987654321
78905.01.2021123456789
32108.01.2021987654321
65412.01.2021345876112
98715.01.2021345876112

 

Table B - deal interval

deal_idstart_dateend_dateproduct_id
102.01.202104.01.2021987654321
205.01.202105.01.2021

123456789

 

I'd like to map the deal_id from table B to the affected order_ids in table A.

In other words, check for each row in table A if table_A.timestamp is between (>= && <=) table_B.start_date and table_B.end_date and if table_A.product_id = table_B.product_id.

If theres a match, get the corresponding deal_id into the new calculated column in table A for further uses in the overall data model.

 

I tried different approaches with DAX statements in a calculated column but I'm not getting quite there..

 

I'd be greatful for every help on how to solve this 🙂

 

Best,

Jano

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Jano , Create a new column in table 1


New column =
maxx(filter( Table2, Table1[product_id] = Table2[product_id] && Table2[start_date] <=Table1[Date] && Table2[end_date] <=Table1[Date] ), Table2[deal_id])

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Jano , Create a new column in table 1


New column =
maxx(filter( Table2, Table1[product_id] = Table2[product_id] && Table2[start_date] <=Table1[Date] && Table2[end_date] <=Table1[Date] ), Table2[deal_id])

@amitchandak worked perfectly fine, thank you!

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.