Lookup help

[jbrines]

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?

[jbrines]

I also menat to say that Actual Time in Power BI is a measure between Created Date and Closed date using NETWORKDAYS

[v-echaithra]

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 dimension

Create 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 measure

SLA 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 Community

Thank you.

[jbrines]

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?

[cengizhanarslan]

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"
)

[jbrines]

Hi @cengizhanarslan do you mean like below?

[cengizhanarslan]

No, please check the following document

https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

[BeaBF]

@jbrines Hi! Can you share the pbix file with tables? or paste data so that i can copy them directly?

Thx

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[jbrines]

@BeaBF how do you upload the pbix file?

[jbrines]

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

[BeaBF]

@jbrines Hi!
Here the calculated column:

FINAL AGGREGATED =

VAR SLA_DAYS =

    LOOKUPVALUE(

        'SLA Time'[Sum of Time (days)],    

        'SLA Time'[Category],              

        'Results Data'[Category]            

    )

VAR DELTA =

    'Results Data'[Actual Time] - SLA_DAYS

RETURN

IF(DELTA > 0, "YES", "NO")

I attack also the pbix file! 🙂
Let me know
BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[BeaBF]

@jbrines You can also do it without the lookup function, but using relations and RELATED function
I attack the same pbix with the second logic implemented

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[Ashish_Mathur]

Hi,

Based on the 2 tables that you have shared, show the expected result.

[jbrines]

Hi @Ashish_Mathur ,

Basically in the mesure column I want it to look at the Category in the results data table, compare it to the Category on Table SLATime Data then use the Sum of Time field to subtract from the Actual Time on the results data table if it is zero or poistive then the outcome should be Yes, if it is a negative the outcome should be No. The Actual Time Column the Results Table in Power Bi is currently a messure column

I hope that makes sense.

[ryan_mayu]

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