Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

LookupValue wth Filter a directquery

Hey Guys,

 

I'm Stuck on a problem.

I have two different Tables, one is a directquery so i cant really see the data and one is imported.

 

In the directquery Table there are multiple columns, but i'm only interested in three of these

Table1:

StarttimeDowntimereasonNumber
03.12.2021Coilchange125478
03.12.2021Broke Screw125478
03.12.2021Detection failed125478
05.12.2021Coilchange254987
04.11.2021Coilchange687478
04.11.2021Broke Screw687478

And my imported Table:

Table2:

TimeListIndex
03.12.2021587987456
04.12.2021658758931
05.12.2021458962147
07.12.2021367854725
04.11.2021365487125

 

Now i want a to add a claculated Column on my imported Table, when the TimeList and Startime matches and in Column Downtimereason stands "Coilchange" i want the number.

So my new imported Table2 should look like this:

TimeListIndexNumber_Table1
03.12.2021587987456125478
04.12.2021658758931 
05.12.2021458962147254987
07.12.2021367854725 
04.11.2021365487125687478

 

I already tried something myself with Lookupvalue but im stuck on converting it to the right columns/values:

 

with my actual formula i'm able to add the matching Dates from the unfilterd Table1 into Table 2:

Number_table1 = LOOKUPVALUE(Table1[Startzeit],Table1[Startzeit],Table2[TimeList]

 

Thanks for your help in advance

 

Sincerely 

Jonas

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi Anonymous ,

     

    Please try the following formula:

     

    Column = 
CALCULATE (
    MAX ( 'DQTable$'[Number] ),
    FILTER (
        'DQTable$',
        'DQTable$'[Starttime] = EARLIER ( IMTable2[TimeList] )
            && 'DQTable$'[Downtimereason] = "Coilchange"
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Calculated column across the table is not possible Direct query. Also, you can not use lookupvalue in direct query column

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your answer amitchandak 

      Do you know any other way to get the descriptes solution?

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi Anonymous ,

         

        Please try the following formula:

         

        Column = 
CALCULATE (
    MAX ( 'DQTable$'[Number] ),
    FILTER (
        'DQTable$',
        'DQTable$'[Starttime] = EARLIER ( IMTable2[TimeList] )
            && 'DQTable$'[Downtimereason] = "Coilchange"
    )
)

         

        If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
        Best Regards,
        Winniz
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.