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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
neees78
Advocate I
Advocate I

Calculated Column based on another table

Hello All, 

I've two tables in my project - no relationship exists

Table 1 

dt.startdt.end  jobtyp
13/06/2022 00:1513/06/2022 01:30Travel
17/06/2022 17:4518/06/2022 10:00Travel
28/06/2022 23:1528/06/2022 23:30Travel
07/07/2022 03:3007/07/2022 06:00Travel
24/07/2022 07:3024/07/2022 12:00Logging 
02/08/2022 18:3002/08/2022 20:00Logging 
02/08/2022 20:3002/08/2022 23:00Logging 

 

Table2

jobtypdt.startdt.end
Travel03/06/2022 11:0015/07/2022 00:00
Logging 22/07/2022 06:3003/08/2022 23:00

 

on Table1 - is it possible to have jobtyp column calculated based on Table2 jobtyp value 

using date values only 

 

if Table1(dt.start) >= Table2(dt.start) && Table1(dt.end)<= Table2(dt.end)

then return jobtyp value from Table2

 

Thanks in advance 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@neees78 Try:

Column = MAXX(FILTER('Table2', Table1[dt.start] >= Table2[dt.start] && Table1[dt.end] <= Table2[dt.end]), [jobtyp])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
neees78
Advocate I
Advocate I

@Greg_Deckler  if I have an ID column in both tables, can we add a condition or second filter to make sure it returns correct [jobtyp] or that row, what I found is that some jobs do cross over in times, thus it returns the first [jobtyp] within that time interval. 

 

regards 

 

Greg_Deckler
Super User
Super User

@neees78 Try:

Column = MAXX(FILTER('Table2', Table1[dt.start] >= Table2[dt.start] && Table1[dt.end] <= Table2[dt.end]), [jobtyp])

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler   thanks a lot - it worked perfectly well 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.