Identify the columns that have the same ID

[alfonsoasenjo]

Hello everyone

I'm wondering if somebody can help me find the solution for this query. To summary I want to created a new column, in this case "SLA" that mark "SI" for the columns that have the same key and have "SI" in the column Reiterate and when the subtraction of the newest creation date and last resolution date are less than 7days

I try using If formula, but dont know how to apply it in Power Bi, Im not really sure if it is possible to do it

Any help is really appreciated. Thanks a lot

Maria J.

[AlB · Accepted solution]

@alfonsoasenjo 

What data type is the Reiterated column? Is it not text? Why, if it's being shown as containing "SI". Best if you can share the pbix

There was a small mistake in the previous code, although this won't change the error. VAR diffDays_ must not have the min:

           VAR diffDays_ = maxCreated_ - minResolved

SLA =
VAR reiterateSI_ =
    CALCULATE (
        COUNT ( Table1[Reiterate] ),
        Table1[Reiterate] = "SI",
        ALLEXCEPT ( Table1, Table1[Key] )
    ) > 0
RETURN
    IF (
        reiterateSI_,
        VAR maxCreated_ =
            CALCULATE ( MAX ( Table1[Created] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR minResolved_ =
            CALCULATE ( MIN ( Table1[Resolved] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR diffDays_ =  maxCreated_ - minResolved
        RETURN
            IF ( diffDays_ < 7, "SI" )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[alfonsoasenjo]

Hi, thanks for the reply, the error was in the second "SI", so the best I could think about, was replacing the text "SI" with a number 1.

SLA =
VAR reiterateSI_ =
    CALCULATE (
        COUNT ( Table1[Reiterate] ),
        Table1[Reiterate] = "SI",
        ALLEXCEPT ( Table1, Table1[Key] )
    ) > 0
RETURN
    IF (
        reiterateSI_,
        VAR maxCreated_ =
            CALCULATE ( MAX ( Table1[Created] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR minResolved_ =
            CALCULATE ( MIN ( Table1[Resolved] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR diffDays_ =  maxCreated_ - minResolved
        RETURN
            IF ( diffDays_ < 7, 1)
    )

  Btw. Thank you very much, I was searching for a solution and finally get it thanks to you 😄

[alfonsoasenjo]

Hello! thank you for the prompt reply,

I used the formula that you suggest, but I still have the syntax error.  "Cannot convert "SI" value of type Text to type Integer". Just to clarify there is some blank data in Table1[Resolve] of the tickets that have not been resolved yet.

Thanks you for the help

[AlB]

@alfonsoasenjo 

Of course it is possible.  Create a calculated column on your table:

SLA =
VAR reiterateSI_ =
    CALCULATE (
        COUNT ( Table1[Reiterate] ),
        Table1[Reiterate] = "SI",
        ALLEXCEPT ( Table1, Table1[Key] )
    ) > 0
RETURN
    IF (
        reiterateSI_,
        VAR maxCreated_ =
            CALCULATE ( MAX ( Table1[Created] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR minResolved_ =
            CALCULATE ( MIN ( Table1[Resolved] ), ALLEXCEPT ( Table1, Table1[Key] ) )
        VAR diffDays_ =
            MIN ( maxCreated_ - minResolved, 0 )
        RETURN
            IF ( diffDays_ < 7, "SI" )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers