Forum Discussion

kleighton's avatar
kleighton
Helper II
5 years ago

Filter a value from another table

Hi,

 

I have the following calculated column in DAX which filters the ordernumber from another table (Work table) based on the start and end date of the runtime event (Runtime table). Can anybody assist in how i could perform this same filter in Powerquery. In certain cases, the ordernumber changes during a runtime event so i will have 2 workorders listed and i should split these into seperate rows (i have a similar custom column to calculate the duration per ordernumber). Unless i'm mistaken, the only place i can split to new rows is within power query.

 

Custom column:

ordernumber =
var MinDate =
MINX(
FILTER(
'Work',
'Work'[machineid] = Runtime[Column1.machineId]
&& 'Work'[Column1.EndDate] > Runtime[Column1.endTime]
),
'Work'[Column1.EndDate]
)
var MaxDate =
MAXX(
FILTER(
'Work',
'Work'[machineid] = Runtime[Column1.machineId]
&& 'Work'[Column1.EndDate] < Runtime[Column1.endTime]
),
'Work'[Column1.EndDate]
)
var OrderNum =
CALCULATE(
VALUES('Work'[Column1.OrderNumber]),
FILTER(
ALL('Work'),
'Work'[machineid] = Runtime[Column1.machineId]
&& 'Work'[Column1.StartDate] <= Runtime[Column1.startTime]
&& 'Work'[Column1.EndDate] >= Runtime[Column1.endTime]
)
)
var MaxOrder =
CALCULATE(
VALUES('Work'[Column1.OrderNumber]),
FILTER('Work','Work'[machineid] = Runtime[Column1.machineId] && 'Work'[Column1.EndDate] = MinDate)
)
var MinOrder =
CALCULATE(
VALUES('Work'[Column1.OrderNumber]),
FILTER('Work','Work'[machineid] = Runtime[Column1.machineId] && 'Work'[Column1.EndDate] = MaxDate)
)
return
IF( ISBLANK(OrderNum), CONCATENATE(MinOrder,MaxOrder), OrderNum )
 

 

 
Thanks in advance,
Keith
 
 
Need Help

1 Reply