cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Will_Rayner
Regular Visitor

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

1 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

View solution in original post

5 REPLIES 5
Will_Rayner
Regular Visitor

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:

Will_Rayner_0-1635428653327.png

Thanks in advance

 

Will

 

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

View solution in original post

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
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors