Forum Discussion

David01's avatar
David01
Frequent Visitor
5 years ago

Calculate a value unless a column has a specific value.

Hi I am trying to do a subtraction unless a column has a specific value in it.

 

What Im trying to do is if the value of list = a then return valuea = 2 else do a subtraction valuea - valueb

 

I cant help but feel its simple but I can't see the solution.

 

ListvalueavaluebReturned result
a2232
b321
c431
d541
e651
f761

 

Thanks in advance.

DAX

3 Replies

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

    Hi David01 

    Create a calculated column:

    NewCol =
IF ( Table1[List] = "a", Table1[valuea], Table1[valuea] - Table1[valueb] )

     

    		 Please accept the solution 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.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hello David01 
    You can try this calculated column.

    Result =
    IF(
    Table[List] = "a",
    Table[valuea],
    Table[valuea] - Table[valueb]
    )
  • David01's avatar
    David01
    Frequent Visitor
    5 years ago

    I had figured it out as I retired for the night.

     

    This is what I did

     

    Measure =
    var tab =ADDCOLUMNS('Mytable',"Result",
    if('Mytable'[List] = "a",calculate(sum(Mytable[valuea]) ,'Mytable'[List] = "a")
    , [Dev minus For]))
    return
    sumx(tab,[Result])


    Note [Dev minus For] is a measure that simply does valuea - valueb on all rows that I had origanaly created and reused.

     

    Thanks for your replys and help.