Forum Discussion

alfonsoasenjo's avatar
alfonsoasenjo
Frequent Visitor
5 years ago
Solved

Identify the columns that have the same ID

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 s...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    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 

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
5 years ago

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's avatar
    alfonsoasenjo
    Frequent Visitor
    5 years ago

    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 😄