cancel
Showing results for
Did you mean:

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

## Calculated Column based on another table

Hello All,

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

Table 1

 dt.start dt.end jobtyp 13/06/2022 00:15 13/06/2022 01:30 Travel 17/06/2022 17:45 18/06/2022 10:00 Travel 28/06/2022 23:15 28/06/2022 23:30 Travel 07/07/2022 03:30 07/07/2022 06:00 Travel 24/07/2022 07:30 24/07/2022 12:00 Logging 02/08/2022 18:30 02/08/2022 20:00 Logging 02/08/2022 20:30 02/08/2022 23:00 Logging

Table2

 jobtyp dt.start dt.end Travel 03/06/2022 11:00 15/07/2022 00:00 Logging 22/07/2022 06:30 03/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

1 ACCEPTED SOLUTION
Super User

@neees78 Try:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3

@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

Super User

@neees78 Try:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors