Filtering a lookup column

[Anonymous]

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.

[Anonymous]

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 · Accepted solution]

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]

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

[Anonymous]

WOW! it worked! 

Thank you