DAX script to calculate Rating for closed incidents

[samurai_jack]

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.

[v-zhangti · Accepted solution]

Hi @samurai_jack ,

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.

[samurai_jack]

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?

[v-zhangti]

Hi, @samurai_jack 

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