Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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
@Will_Rayner Use MAXX(FILTER(...),...) instead. You basically want a LookupValue Range it sounds like: LOOKUPVALUE Range - Microsoft Power BI Community
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |