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 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 

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

7 Replies

  • 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

  • MattAllington's avatar
    MattAllington
    Community Champion
    8 years ago

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

    • 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