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

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
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