cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## DAX script to calculate Rating for closed incidents

Hi all, I have a data set where I need to define a rating system from 1 to 5 for closed incidents, and the criteria is as follows;

1 - No monthly reporting
2 - 50% incidents closed within 75 days after opening and only 25% or less incidents open more than 6 months
3 - 70% incidents closed within 75 days after opening and only 10% or less incidents open more than 6 months
4 - 80% incidents closed within 75 days after opening and no incidents open more than 6 months
5 - 100% incidents closed within 75 days after opening

I have fields incident_created_at and incident_closed_in_days as dummy variables.

Can you please help me with this? I am stuck defining the percentage range here.

Thank you very much.

1 ACCEPTED SOLUTION
Community Support

You can try the following methods.

Column:

``Days = [incident_closed_in_days]-[incident_created_at]``

Measure:

``````Rating =
VAR _N1 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[incident] ),
FILTER ( ALL ( 'Table' ), [Days] <= 75 )
),
COUNT ( 'Table'[incident] )
)
VAR _N2 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[incident] ),
FILTER ( ALL ( 'Table' ), [Days] >= 180 )
),
COUNT ( 'Table'[incident] )
)
RETURN
SWITCH (
TRUE (),
_N1 = 1, 5,
_N1 >= 0.8 && _N2 = 0, 4,
_N1 >= 0.7 && _N2 <= 0.1, 3,
_N1 >= 0.5 && _N2 <= 0.25, 2,
1
)``````

Please check the attachment, is it the result you are expecting?

Best Regards,

Community Support Team _Charlotte

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

3 REPLIES 3
Community Support

You can try the following methods.

Column:

``Days = [incident_closed_in_days]-[incident_created_at]``

Measure:

``````Rating =
VAR _N1 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[incident] ),
FILTER ( ALL ( 'Table' ), [Days] <= 75 )
),
COUNT ( 'Table'[incident] )
)
VAR _N2 =
DIVIDE (
CALCULATE (
COUNT ( 'Table'[incident] ),
FILTER ( ALL ( 'Table' ), [Days] >= 180 )
),
COUNT ( 'Table'[incident] )
)
RETURN
SWITCH (
TRUE (),
_N1 = 1, 5,
_N1 >= 0.8 && _N2 = 0, 4,
_N1 >= 0.7 && _N2 <= 0.1, 3,
_N1 >= 0.5 && _N2 <= 0.25, 2,
1
)``````

Please check the attachment, is it the result you are expecting?

Best Regards,

Community Support Team _Charlotte

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

Helper I

Thank you, the rating seems to work!

But I cannot add this measure as a filter or slicer. Is there anything additional I need to do from my side to add it as a filter/slicer?

Community Support

Measure cannot be added as a slicer directly. But you can try a page level Filter. What kind of result do you want to achieve? You can show it with pictures.

Best Regards