SLA Breach User List

DAX DAX Calculations
1

Hi team,

I am stuck with in a scenario , wherein i need to identify the list of users who have breached a particular criteria each month based on Start date and end date column.

Example if a person A has again breached a particular SLA , he needs to be highlighted which gives a impression to the end user that he had breached the SLA in previous month as well.I have created a calculated column Abuser list based on which i need to identify the user

I have attached the PBIX with Sample data.

Issue 2
Also i was trying to calculate the sum of particular value using the formula

Total OG Calls_Test = CALCULATE(SUM(Sheet1[TOT_OUTGOING_CALLS]),

FILTER(VALUES(sheet1[MI_FROM_DATE]),Sheet1[MI_FROM_DATE] <= Max(‘Invoked Function’[Date])),

FILTER(VALUES(Sheet1[MI_TO_DATE]),Sheet1[MI_TO_DATE] >= Min(‘Invoked Function’[Date])))

But some how when i select a particular calendar month it gives me wrong result.The number for each day is very high not sure whether is getting rolled up

sample.pbix (421.8 KB)

Kindly request you to help please.

2

Hi Vishy,

Have you made one of the relation between Sheet1 and the Date table active?

3

@Vishy,

@uriah1977 is correct. You need an active relationship between your Date table and your fact table for time intelligence functions to work correctly. In addition, you need to mark your Date table as a date table, and validate your Date field. For issue #2, all you need is your simple [Total OG Calls] measure. All your extra CALCULATE and FILTER statements are telling DAX to do is calculate over the range of your date table, which your simple measure will do once you make the changes above, and provide the proper evaluation context per the table below:

image

Per issue #1, I’m having a hard time figuring out what you’re looking for. Can you please provide a mockup of the result you want to see?

Thanks.

4

Yes agree with all of the comments above. There is some fundamental learning around DAX which is required here.

How to recommend running through the below course as soon as you can as this is all covered in quite a lot of detail.

Sam

5

Hi ALL,

Thanks alot for all the quick responses , however I had done the calculation based on the single active relationship and the answer is coming correctly. But i saw one of the enterprise DNA video , wherein we can create the calculation over a range using the inactive relationships and the above DAX which is specified. i.e. filter and values. Using a single active relationship i am already getting values as said by @BrianJ.

But what about the values which fall between october and November example screen shot provided . How to handle those records because currently with single active relation ship it showing them in the month of october only. And if at all user requirement is that want to exclude such values who have not completed the month cycle yet.

Image
Image.PNG1394×628 68 KB

In my second requirement,
Basically I am planning to highlight only such records in a table visualization who have being high risk or medium risk , more than once previously i.e. lets say a person A was high risk in the month of June , July and August . Such users needs to be marked red , giving end user indication that this person was under high risk previously as well in the current month view.
For this what my plan is to write a dax of the format -

Abuser Count =
CALCULATE(COUNTROWS(Sheet1),ALLEXCEPT(Sheet1,Sheet1[MI_Subcription_ID],Sheet1[Abuser Descr] ))

This measure is going to give me the count of rows based on Abuser descr and then output of this can be used in the conditional formatting in the current month view table visualization.

Kindly request you to help me with the same.

6

To solve these multiple dates scenarios, where you need to show how much sets within or between two dates, you need to use the below technique.

The concepts are also detailed in the below tutorials.

Just make sure to follow the technique exactly here including setting up the relationships correctly in the model.

Sam

7

HI Sam,

Thanks for the quick help, i did see this video before and base on that created the measure as required.

I am actually confused with the data that i have because in it for each row the start and end date over laps as a result of which using the above formula i get values summed up at that particular intersection of the dates i.e. see the screen shot below -

image

This is because the end date of one row is the start date for the 2nd row. I consider this to be the data issue and needs to be resolved in the source. then the formula suggested by you would work correctly.
What is your opinion on the same.

image
image.png1178×128 6.02 KB

8

You can also just simply change up the formula so instead of <= (less than or equal to) you could just have < (less than) for instance.

Sam