SLA Range/Grouping Dax

[Shree_185]

Hi,

Can anyone help me here?

I have "Pending Days" field in D:hh:mm: ss format - ex- 1 day 00:30:00. I want to group them on 0-20 , 21 to 40, 41 to 60 days.......likewise 

Logic: if "Pending Days" come in 0-20 days then "0-20 group" likewise 

Ticket No	pending in days	Groups
12345	0 Day 00:19:00	0-20
45678	23 Day 00:09:00	21 -40
54678	53 Day 00:19:00	41 -60
6789	41 Day 00:19:00	41-60

[Shree_185]

Hi,

Can anyone help me here?

I have "Pending Days" field in D:hh:mm: ss format - ex- 1 day 00:30:00. I want to group them on 0-20 , 21 to 40, 41 to 60 days.......likewise 

Logic: if "Pending Days" come in 0-20 days then "0-20 group" likewise 

Ticket No	pending in days	Groups
12345	0 Day 00:19:00	0-20
45678	23 Day 00:09:00	21 -40
54678	53 Day 00:19:00	41 -60
6789	41 Day 00:19:00	41-60

[MFelix · Accepted solution]

Hi @Shree_185 ,

You can create a calculated column wiht the following syntax:

Days Group =
SWITCH (
    TRUE (),
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "20", "0-20",
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) > "20"
        && LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "40", "21-40",
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) > "40"
        && LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "60", "41-60",
    ">60"
)

[v-xiaotang · Accepted solution]

Hi @Shree_185 

If you want to create a measure, you can try this,

Measure = 
var _getDay= INT(LEFT(MIN('Table'[pending in days]),SEARCH("D",MIN('Table'[pending in days]),,BLANK())-2))
return SWITCH(TRUE(),
_getDay>=0 && _getDay<21,"0-20",
_getDay>=21 && _getDay<41,"21-40",
_getDay>=41 && _getDay<61,"41-60"
)

Best Regards,

Community Support Team _Tang

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

[Shree_185]

Thank you, you made my day!!!

[MFelix · Accepted solution]

Hi @Shree_185 ,

You can create a calculated column wiht the following syntax:

Days Group =
SWITCH (
    TRUE (),
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "20", "0-20",
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) > "20"
        && LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "40", "21-40",
    LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) > "40"
        && LEFT ( 'Table'[pending in days], FIND ( "Day", 'Table'[pending in days] ) - 2 ) <= "60", "41-60",
    ">60"
)