Show lines that match in between dates from another unrelated table

[Anonymous]

Hi,

I have two separate tables which cannot be related to each other (two different sources). One is called reference table in which a workorder can be selected, the other is error_code.  Functionality should be that when I filter a specific workorder in the reference table, there is a 'realBeginTime' and 'realEndTime' present in that table. The related error codes then should be displayed for the time between realbegintime and realendtime. So there should be a check that 'datStart' (see picture) from the error_code table is between realbegintime and realendtime from the reference table. In beneeth picture only the red marked lines should appear in the table.

[v-xicai]

Hi @Anonymous ,

You can create measure Filter1 in reference table, then put measure Filter1 in the Visual Level Filter of goal table visual, and setting the Filter1 as "is not blank".

Filter1=IF(COUNTROWS ( ALLSELECTED ( reference[reference] ) ) < 1,BLANK (),IF(reference[realBeginTime]<=MAX(error_code[datStart])&&reference[realEndTime]>=MAX(error_code[datStart]),1,BLANK()))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi,

Thanks for your assistance. After the second if statement I cannot select 'reference[realBeginTime'. Only a formula or measure. Any idea how to solve?

Cheers.

[v-xicai]

Hi @Anonymous ,

Please try this measure.

Filter1=IF(COUNTROWS ( ALLSELECTED ( reference[reference] ) ) < 1,BLANK (),IF(MAX(reference[realBeginTime])<=MAX(error_code[datStart])&&MAX(reference[realEndTime])>=MAX(error_code[datStart]),1,BLANK()))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Amy,

I tried following dax formula:

Check = IF(COUNTROWS(ALLSELECTED(OrderView[reference]))<1,BLANK(),IF(MAX(OrderView[realBeginTime])>=MAX(slaveStateMarker[datStart])&&MAX(OrderView[realEndTime]<=MAX(slaveStateMarker[datStart])),1,BLANK()))

I am working on direct query and get the message: "the max function only accepts a column reference as an argument"

[v-xicai]

Hi  @Anonymous ,

Does that make sense? If not, let me know and I'll try to help you further.

Best regards

Amy

[v-xicai]

Hi @Anonymous ,

Is there anyone is measure for [realBeginTime], [realEndTime] and [datStart]? If yes, you can delete the MAX function before this date measure , and try it again. Or you may put bucket ")" in wrong place for MAX function.

Best Regards,

Amy

[Anonymous]

Solved by following calculation:

Time_Range =

VAR

Start_error_code = VALUES(slaveStateMarker[datStart])

VAR

Begin_time_op_seq = LOOKUPVALUE(OrderView[realBeginTime],OrderView[reference],ALLSELECTED(OrderView[reference]))

VAR

End_time_op_seq = LOOKUPVALUE(OrderView[realEndTime],OrderView[reference],ALLSELECTED(OrderView[reference]))

RETURN

IF(Start_error_code>=Begin_time_op_seq && Start_error_code<=End_time_op_seq,1,0)

[parry2k]

@Anonymous can you post some sample data in excel by sharing thru onedrive/google drive.