Forum Discussion
Lookup help
Hi guys,
I am converting an excel spreadsheet (below) in to Power BI.
I have a Table names SLATime which is a list categories with an SLA Time for each category. I want to create a messure (SLA Met) that will lookup the SLATime table and compare it to the existing category in the results table then subtract the actual time from the SLATime and if it is positive then Produce Yes and if it is negative then Produce a No. Currently in the spreadsheet I am having to use multiple colums to do this. Is it possible and if yes what would the DAX formula be?
18 Replies
Hi BeaBF
Results data
Ticket Subject Created Date Closed Date created_by Owner Category Sub-Category Actual Time IT-22865 Force Check In 02/11/2025 17:34 03/11/2025 07:35 Shannon Kelly Jonathon Hewitt PIM Force Check In 0 IT-22866 Suspicious Email Reported by 03/11/2025 07:22 03/11/2025 08:49 Ian Stewart Scott Young Suspicious Email Genuine 0 IT-22867 PIM NOT WORKING 03/11/2025 07:38 03/11/2025 09:12 Macauley Wilson Jonathon Hewitt PIM Local File Management 0.025 IT-22868 Suspicious Email Reported by 03/11/2025 07:57 03/11/2025 09:07 Barry McAulay Scott Young Suspicious Email Phishing Test 0.014583333 IT-22869 Access to site sign in 03/11/2025 08:21 03/11/2025 08:33 Georgie Chisholm John Brines Software Microsoft Teams 0 SLATime Data
Category Sum of Time (days) Account Lockout 0.042 BT Cloud Phone 0.5 Business Central 3 Cisco Duo 0.042 Conquest 1 Desk Move 2 Dime 3 Door Cards 1 Email 0.5 Email Photo Update 2 Folder Access 1 FortiClient 0.5 GDPR Delete 1 Hardware - Office 1 Hardware - Site 3 Internet 1 Jet 1 Laptop Build 5 Mailbox Access 1 Mobile Phone - Office 2 Mobile Phone - Site 3 Net 2 Access Control 0.5 New Email Address 1 Password Reset 0.042 PIM 1 Printer 1 Software 1 Spam Issue 0.084 Suspicious Email 0.084 User Account Change 1 how to turn SLATime Data into result data? could you pls elaborate more on this ?
pls provide the sample data and the expected output based on the sample data you provided
Best option would be creating your model in star-schema(create DimCategory and create relations from dim to Fact tables) and then usign the measure below:
SLA Met = IF( SELECTEDVALUE(Results[Actual Time]) <= SELECTEDVALUE(SLATime[SLA Time]), "Yes", "No" )No, please check the following document
https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
I also menat to say that Actual Time in Power BI is a measure between Created Date and Closed date using NETWORKDAYS
Hi jbrines ,
This is possible, and you do not need multiple columns like in Excel.
The key is to model it correctly and use a measure-to-measure comparison.
Create a Category dimensionCreate a DimCategory table containing distinct Category
Results[Category] > DimCategory[Category]SLATime[Category] > DimCategory[Category]
This creates a proper star schema, which is required for clean DAX.
Keep Actual Time as a measure
Since Actual Time is already a measure (calculated using NETWORKDAYS), that’s perfectly fine.Create an SLA Time measure
SLA Time =
SELECTEDVALUE ( SLATime[Sum of Time (days)] )
Create the SLA Met measureSLA Met =
IF (
[Actual Time] <= [SLA Time],
"Yes", "No")If possible, could you please provide more details about your data?
How to provide sample data in the Power BI Forum
You can refer the following link to upload the file to the community.
How to upload PBI in CommunityThank you.
Hi v-echaithra , Sorry I don't have a lot of experinec with Power BI especially Dimentions.
Create a Category dimension - Is this a new Dimention Table called Category?
Create a DimCategory table containing distinct Category - My SLATime Data table contains distinct Categories.
Is there a way I can send you the pbix so you can then show me what you mean?
