Skip to main content
cancel
Showing results for 
Search instead 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

Reply
samurai_jack
Helper I
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
v-zhangti
Community Support
Community Support

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
    )

 

vzhangti_0-1649052246672.png

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.

View solution in original post

3 REPLIES 3
v-zhangti
Community Support
Community Support

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
    )

 

vzhangti_0-1649052246672.png

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.

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?

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.

vzhangti_0-1649059149979.png

 

Best Regards

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors