Forum Discussion

Will_Rayner's avatar
Will_Rayner
Frequent Visitor
4 years ago
Solved

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

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

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

    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

5 Replies

    • Will_Rayner's avatar
      Will_Rayner
      Frequent Visitor
      4 years ago

      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

  • Will_Rayner's avatar
    Will_Rayner
    Frequent Visitor
    4 years ago

    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's avatar
      v-janeyg-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      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's avatar
        Will_Rayner
        Frequent Visitor
        4 years ago

        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