Filter two columns lookup

[dom]

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 timeline_stream table I have actual data which are setups durations and I would like to compare it with target setup times from table reasons_target. Every line has defined different target times for "flush" and "break", so what I would like to achieve is this kind of visual:

Line ID               setup type    actual setup time    target     diff        date  

A101                      flush                     28                   15          13          28/10/2017

A102                      flush                      19                   18           1           28/10/2017

I tried relationship in both direction from reasons_target table to line_id table but couldnt get it working.

I`m fairly new to power bi and data modeling so I would really appreciate if someone could help me.

Thanks

Dom 

[v-ljerr-msft · Accepted solution]

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. 

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]

thank you so much v-ljerr-msft 

This is exactly what I was looking for. 

Would you mind explaining step by step how it works please

Regards

Dom

[v-ljerr-msft]

Hi @dom,

The formula could be easy to understand if you understand row context and filter context in DAX. The follow articles are for your reference.

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

If you have any question, feel free to post back or in new thread. 

Regards

[dom]

Thanks, i will definately look at it

Regards Dom

[MattAllington]

If you want to filter on setup type and date, you need common lookup tables for these too - just like line ID

[dom]

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]

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