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
frano72
Helper IV
Helper IV

looking up a start and end date between another tables start and end date

Hi, i can do the following in DAX but i think it might be better to do it in PQ.

 

I have one fact table that is storing the "state" of a piece of equipment (dozer).  Is it "pushing", is it "idling" etc.  There is a start time, an end time, and the state name for this table.

 

Another fact table stores cycle data.  Similar format, a start time an end time and some other metrics.

 

So typically there are many cycles for each state.

 

So in the cycle fact table i want to add a column that brings over from the states table what state was it in for that cycle.

 

So a really simple example could be, you might have two records in state table - 12 am to 1 pm "pushing", 1 pm to midnight" "idling".

startfinishstate
12:00 am1 pmpushing
1 pmmidnightidling

 

Then in cycles table, there can be 100's of rows...so it needs to lookup the start and end time over in the states table to return which state it is in.

 

start timeend timemetres NEW COLUMN (STATE)
12:0012:0350function returns "Pushing"
12:0312:0755function returns "Pushing"

 

hopefully this makes sense.  ignoring the business meaning of the data, its a case of finding where a time interval is between another (larger) time interval.  one can assume that you never get a time interval crossing boundaries.

 

 

 

 

1 ACCEPTED SOLUTION
wdx223_Daniel
Super User
Super User

@frano72 

wdx223_Daniel_0-1604295419366.png

let
    StateTable = Excel.CurrentWorkbook(){[Name="StateTable"]}[Content],
    StatesTable = Excel.CurrentWorkbook(){[Name="StatesTable"]}[Content],
    Custom1 = Table.AddColumn(StatesTable,"State",each Table.SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]){0}?[state]?)
in
    Custom1

View solution in original post

5 REPLIES 5
wdx223_Daniel
Super User
Super User

@frano72 

wdx223_Daniel_0-1604295419366.png

let
    StateTable = Excel.CurrentWorkbook(){[Name="StateTable"]}[Content],
    StatesTable = Excel.CurrentWorkbook(){[Name="StatesTable"]}[Content],
    Custom1 = Table.AddColumn(StatesTable,"State",each Table.SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]){0}?[state]?)
in
    Custom1

Hi @wdx223_Daniel , that works a treat.  Now for scope creep 😉  I left out another part of the issue to simplify the problem.  within both fact tables there is a column that is the uniqueID for the Dozer.  So if you can imagine in your two tables a column added to each with a unique dozer name.  

 

So somewhere in this statement need to also ensure statestable.machineid = statetable.machineid

SelectRows(StateTable,(x)=>x[start]<=[start time] and x[finish]>=[end time]

 

make sense ?

 

thanks !

 

actually...i think i got it to work.  just added another and condition following the same format and it looks to be working.

 

Thanks @wdx223_Daniel 

CNENFRNL
Community Champion
Community Champion

Hi, @frano72 , to my understanding, what's needed to do is to compare the end time with the division point, i.e. 1pm in the mockup.

#"New Column" = Table.AddColumn(#"cycles table", "Status", each if [end time]>#time(13,0,0) then "pushing" else "idling")

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Hi @CNENFRNL  - that certainly would work for the specific case listed, but those fact tables are continually updated with new data, so need a way for it to be generalised so that can look up what the state is rather than hard coding it in the if statement.

 

I thought there would be a way of parameterising the problem with a custom power query function that does the date check

 

Does that make sense ?

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
Top Kudoed Authors