Bringing back a LOOKUP value from a table using text field and date ranges

[Will_Rayner]

Hi,

I am assessing SLA adherance and need to bring back an SLA numeric value from a separate table. I'm having trouble linking an item to the SLA detail as its based on matching multiple fields.

A LOOKUPVALUE function is needed based on "SLA NAME" (a text desccription of the SLA) and the COMPLETED DATE of the item in in my "INPUT" table

The destination table holding the SLA details has the SLA NAME (which is not unique), "EFFECTIVE_FROM" and "EFFECTIVE_TO" dates, and the value "DAYS" I'd like to return. 

I would like to reference the "DAYS" value into the input table, and/or include it in a measure against elapsed days for processing, based on the SLA NAME matching, and the COMPLETED DATE being >= EFFECTIVE_FROM date and <= EFFECTIVE_TO date

Is this possible?

Many thanks

Will

[Will_Rayner]

Hi Greg,

I have tried to implement the suggested structure of measure however it's not returning any value. Am I missing something? I'm sure I'm nearly there!

Tables and DAX used are as below:

Thanks in advance

Will

[v-janeyg-msft · Accepted solution]

Hi, @Will_Rayner 

You don't need to use .date format in code.

I modify your code, Like this:

SLA Target =
MAXX (
    FILTER (
        ALL ( 'sla' ),
        [SLA Code] = SELECTEDVALUE ( 'UW Input for SLA'[SLA Type Name] )
            && [Effective From] <= SELECTEDVALUE ( 'UW Input for SLA'[END_DATE] )
            && [Effective To] >= SELECTEDVALUE ( 'UW Input for SLA'[END_DATE] )
    ),
    [SLA Value]
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Community Support Team _ Janey

[Will_Rayner]

Yes! That's doing exactly what I need it to. The SLA target values are being selected based on the Type ,and the End Date being within the Efftv from and to dates!

Thank you. Solution accepted!

Will

[Greg_Deckler]

@Will_Rayner Use MAXX(FILTER(...),...) instead. You basically want a LookupValue Range it sounds like: LOOKUPVALUE Range - Microsoft Power BI Community

[Will_Rayner]

Thanks for the suggestion and link. I'll investigate further.

I'm just starting my PBI journey so very much appreciate nuggets of help like this!

Will