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. ...
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

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