Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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".
start | finish | state |
12:00 am | 1 pm | pushing |
1 pm | midnight | idling |
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 time | end time | metres | NEW COLUMN (STATE) |
12:00 | 12:03 | 50 | function returns "Pushing" |
12:03 | 12:07 | 55 | function 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.
Solved! Go to Solution.
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
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
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 ?