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]
    )

     

     

icturion's avatar
icturion
Resolver II
4 years ago

Hi Samarth_18 ,

 

Thank you for the quick respons. 

 

It's just not quite the solution I'm looking for yet. The inherit value from type D for type C only applies to Type C if Type D has the same ID. Suppose Type A also has the same ID, then it may not have the interval of type D. Type A and others must always keep its own interval value. Is this possible?

 

Thanks,

Samarth_18's avatar
Samarth_18
Community Champion
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]
    )