topic Re: SLA MET AND NOT MET: in DAX Commands and Tips

SLA MET AND NOT MET:

Anonymous — Tue, 05 Apr 2022 20:01:09 GMT

Hi Team,

Please help me to calculate Dax for SLA load Met/Not (window hours is 6 hours for all)

Calculated Load sla time as below which is working fine but unable to calculate met and not met from it.

LoadSLATime = datediff(MAX('Completeness Data Pipeline Execution Details'[NIFI Start Time]), max('Completeness Data Pipeline Execution Details'[Data Pipeline End]), minute)/60

SLA Load MET = IF('Completeness Data Pipeline Execution Details'[LoadSLATime]<='Table Master'[Data Load Window Hours], TRUE(),FALSE())

Sla load met is not working correctly ,please find the yellow highliter :

Re: SLA MET AND NOT MET:

tackytechtom — Tue, 05 Apr 2022 20:40:55 GMT

Hi Anonymous ,

How are the tables Completeness Data Pipeline Execution Details' and 'Table Master' connected with each other? Also what is the data type of the Data Load Window Hours column?

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: SLA MET AND NOT MET:

Anonymous — Tue, 05 Apr 2022 20:49:07 GMT

Hi Tom,

yes both table are connected. Data type is datetime for window hours.

Re: SLA MET AND NOT MET:

Anonymous — Tue, 05 Apr 2022 20:54:27 GMT

looks like SLA Met/not Met Dax logic is incorrect. Please help.

Re: SLA MET AND NOT MET:

Whitewater100 — Wed, 06 Apr 2022 01:18:38 GMT

Hi:

Please find one potential solution. As mentioned earlier it required a little changing of data types. Please see link here. I made up figures but had a few of yours in there. I could have combined the goal column into the final column but thought you might want to see it.

https://drive.google.com/file/d/1DW33FTVMJ0Z5SqAWiBGAFzizPmww91oB/view?usp=sharing

Here is the excel source data link I used if you want to see the steps. I hope this helps and solves the question.

https://docs.google.com/spreadsheets/d/1uFTqyjUKJZLTvs1-Kbc5cjSEmz-RITy1/edit?usp=sharing&ouid=107858444427060984721&rtpof=true&sd=true

Re: SLA MET AND NOT MET:

Anonymous — Wed, 06 Apr 2022 06:13:46 GMT

Hi,

i am unable to create goal column. Please find the error below:

Thanks,

Re: SLA MET AND NOT MET:

tackytechtom — Wed, 06 Apr 2022 06:18:24 GMT

Hi Anonymous ,

I haven't looked into the file, but it seems like you are trying to create a measure while you are saying to would like to create a column 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

Re: SLA MET AND NOT MET:

Anonymous — Wed, 06 Apr 2022 06:21:21 GMT

Hi Tom,

I tried both measure and column but its not working for me.

Re: SLA MET AND NOT MET:

Whitewater100 — Wed, 06 Apr 2022 12:59:39 GMT

Hi:

What Tom said looks accurate.

Can you create the goal as calculated column? Because the goal forumla refers to the first field (NISI..)I would check to make data type Date/Time.

If you want to make a measure you can do:

Goal 2 =

var starttime = SELECTEDVALUE('Completeness Data Pipeline Execution Details'[NIFI Start Time])

var datacompleteness = SELECTEDVALUE('Completeness Data Pipeline Execution Details'[Data Load Window Hours])

var fracofday = DIVIDE(datacompleteness, 24)

return

starttime + fracofday

Here is a table on report canvas:

Re: SLA MET AND NOT MET:

v-yanjiang-msft — Tue, 19 Apr 2022 09:11:37 GMT

Hi Anonymous ,

Has your problem been solved? If yes, please consider accept it as the solution to help the other members find it more quickly.

If not, simply change the data type of [Data Load Window Hours] to number, because result of the LoadSLATime is a number but not a time.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.