Forum Discussion

Rana80's avatar
Rana80
Frequent Visitor
2 years ago
Solved

CONTAINSSTRING with counting rows

Hi 

 

I'm  trying to create an calculated column that counts how many times the phrase 

"marked this task as resolved" occurs for each Task ID

 

I have done this with a calculated column call 'Time to Complete Flag' by;

 

Time to Complete Flag =

    IF(
    CONTAINSSTRING( crda9_messenger_messages[Message], "marked this task as resolved" ),
    1,
    BLANK()

 

Current:

 

What i want it to do is it increment the count by 1 if there is more than 1 iteration of  "marked this task as resolved". For Task ID AAA & BBB there is only one iteration of the text. However for Task ID CCC has 2 interations and i want it to count these  in asending order from the Created On date field so the output looks like the below so i can perform a max query on the next analysis.

 

I'm sure my CONTAINSTRING needs extending but i'm unsure how in DAX.

 

Need:

Thank you all!

Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

    Column = VAR t1 = 'Table'[Task JD]
VAR t2 =
    CONTAINSSTRING('Table'[Message],"marked this task as resolved")
VAR tbl =
    FILTER(ALL( 'Table' ),
        'Table'[Task JD] = t1&& 
            CONTAINSSTRING('Table'[Message],"marked this task as resolved" )= TRUE)
RETURN
    IF( t2, RANKX( tbl, 'Table'[Created On], , ASC, DENSE ) )

2 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    pls try this

    Column = VAR t1 = 'Table'[Task JD]
VAR t2 =
    CONTAINSSTRING('Table'[Message],"marked this task as resolved")
VAR tbl =
    FILTER(ALL( 'Table' ),
        'Table'[Task JD] = t1&& 
            CONTAINSSTRING('Table'[Message],"marked this task as resolved" )= TRUE)
RETURN
    IF( t2, RANKX( tbl, 'Table'[Created On], , ASC, DENSE ) )