Forum Discussion

icturion's avatar
icturion
Resolver II
4 years ago
Solved

calculated column based on row values

Hi,   Does anyone know how to add a column to a table by taking a value from another column depending on whether records in another column have the same id? Exmaple table: ID Type Interval ...
Need Help
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    icturion Please try this:-

    Result =
VAR _typeD =
    CALCULATE (
        MAX ( 'Table (3)'[Interval] ),
        FILTER (
            ALL ( 'Table (3)' ),
            'Table (3)'[ID] = EARLIER ( 'Table (3)'[ID] )
                && 'Table (3)'[Type] = "D"
        )
    )
RETURN
    IF (
        'Table (3)'[Type] = "C",
        IF ( NOT ( ISBLANK ( _typeD ) ), _typeD, 'Table (3)'[Interval] ),
        'Table (3)'[Interval]
    )

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

icturion , Try a new column like

 

new column =
var _1 = countx(filter(Table, [ID] =earlier([ID])), [ID])
var _2 = countx(filter(Table, [ID] =earlier([ID]) && [Type] = "D"), [Interval])
return
Switch(True() ,
_1 <= , [Interval]
_1 >1 && [Type] = "C" , _2 ,
[Interval]
)

icturion's avatar
icturion
Resolver II
4 years ago

Hi amitchandak ,

 

Thank you for your respons, if i try your code, i get an error message

 

The SWITCH function does not support comparing values ​​of type True/False with values ​​of type Integer. Consider using the VALUE or FORMAT function to convert one of the values. 

 

The ID column is Whole Number

The Type column is text

The Interval column is decimal

 

Do you know what i need to change?