Forum Discussion

Phellipe's avatar
Phellipe
New Member
2 years ago
Solved

Refer to multiple column values

Hi, looking for some advice

i got this table and i need to add adittional column which will balance negative numbers - currently on line for SQL SRV 2018 Ent Core 2017 there is "-186" and new output should be 0 and row for SQL SRV 2022 Ent Core should be by 186 less (760-186)

- similary for Standard - 2019 would be 0 and 2022 version would be less by 324 (460-324)

basicaly apply downgrading licensing option 

any ideas please? 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Phellipe 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    First, add the index column in “Transform data”

     

     

    Click Close & Apply.

     

    Create a measure. Add extra columns to balance negative numbers - currently SQL SRV 2018 Ent Core 2017's rows have "-186", the new output should be 0, SQL SRV 2022 Ent Core's rows should be 186 less (760-186).

     

    New_Delta = 
    var _delta = IF(
        SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), 
        VALUES('Table'[Delta]), 
        CALCULATE(
            ABS(MIN('Table'[Delta])), 
            FILTER(
                ALL('Table'), 
                'Table'[Index] = MAX('Table'[Index]) -1
            )
        )
    )
RETURN 
    IF(
        SELECTEDVALUE('Table'[Delta]) > 0 
        && 
        SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), 
        VALUES('Table'[Delta]), 
        SELECTEDVALUE('Table'[Delta]) - _delta
    )

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

3 Replies

  • talespin's avatar
    talespin
    Solution Sage
    2 years ago

    hi Phellipe ,

     

    There must be some order(A column that makes sure that records are ordered correctly before performing calculation) to achieve this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Phellipe 

     

    For your question, here is the method I provided:

     

    Here's some dummy data

     

    "Table"

     

    First, add the index column in “Transform data”

     

     

    Click Close & Apply.

     

    Create a measure. Add extra columns to balance negative numbers - currently SQL SRV 2018 Ent Core 2017's rows have "-186", the new output should be 0, SQL SRV 2022 Ent Core's rows should be 186 less (760-186).

     

    New_Delta = 
    var _delta = IF(
        SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), 
        VALUES('Table'[Delta]), 
        CALCULATE(
            ABS(MIN('Table'[Delta])), 
            FILTER(
                ALL('Table'), 
                'Table'[Index] = MAX('Table'[Index]) -1
            )
        )
    )
RETURN 
    IF(
        SELECTEDVALUE('Table'[Delta]) > 0 
        && 
        SELECTEDVALUE('Table'[Delta]) <> SELECTEDVALUE('Table'[Entitlements]), 
        VALUES('Table'[Delta]), 
        SELECTEDVALUE('Table'[Delta]) - _delta
    )

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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