Forum Discussion

dom's avatar
dom
Frequent Visitor
8 years ago
Solved

Filter two columns lookup

Hi. Could someone advise please correct approach to this data model:     Both tables are direct query from local sql server. Data are received from production lines A101 and A102. In t...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi dom,

     

    According to your descriptions above, you should be able to use the formulas below to create two measures to calculate target and diff in your scenario. :smileyhappy:

    target = 
CALCULATE (
    MAX ( reasons_target[target_time] ),
    FILTER (
        ALL ( reasons_target ),
        reasons_target[divice_id] = FIRSTNONBLANK ( timeline_stream[line_id], 1 )
            && reasons_target[reason_text] = FIRSTNONBLANK ( timeline_stream[reason_text], 1 )
    )
)

    diff = MAX(timeline_stream[setup duration MINS])-[target]

     

    Regards

dom's avatar
dom
Frequent Visitor
8 years ago

thanks for your reply

What I want to achieve and I`m struggling  is:

for example in timeline_stream table  I have different lines, different setup types and actual setup time  and  I would like to return setup target time depending of setup type (flush, break, etc) and line id. My challenge is different targets for the same setup type on different lines. Would I need lookup table for every single line?

 

many thanks

 

 

dom's avatar
dom
Frequent Visitor
8 years ago

Basically what I need is this kind of logical statement:

IF(timeline_stream[devieKey]=“A101” AND time_line[reason_text] = “flush” THEN RETURN

reasons_targets[target_time] WHERE reasons_targets[device_id] = “A101”

                                               AND reasons_targets[reson_text]=“flush”

 

repeated for all lines_id and reasons