Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filtering a lookup column

 

Hi Everyone, 

I need to retrieve Rate.Value only where Rate.Type = 20 and input it to column in the ServiceTicketline table. Using Serviceticketline.ServiceRateTable and Rate.ServiceRateTable as the join.



 

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    Hi Anonymous,

     

    The formula could be like below. Please refer to Lookupvalue.

     

    lookupColumn =
LOOKUPVALUE (
    'Rate'[Value],
    'Rate'[RateType], 20,
    'Rate'[ServiceRatetable], [ServiceRateTable]
)

    If this can't achieve your goal, please provide a sample.

     

    Best Regards,

    Dale

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    I am looking to only pull back values where rate type is "20"

    Whats the best way to do this? 

    My initial thought was a function like 

    Lookupvalue(
         'Rate'[Value]where {ratetype}=20,
                'Rate'[ServiceRatetable],
                      'Serviceticketline'[ServiceRateTable])

    this function does not work. 

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      8 years ago

      Hi Anonymous,

       

      The formula could be like below. Please refer to Lookupvalue.

       

      lookupColumn =
LOOKUPVALUE (
    'Rate'[Value],
    'Rate'[RateType], 20,
    'Rate'[ServiceRatetable], [ServiceRateTable]
)

      If this can't achieve your goal, please provide a sample.

       

      Best Regards,

      Dale

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Instead of mentioning values to filter, how can I write this to ignore certain values or blanks?