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:

IDTypeInterval
123A1
234B2
345C3
345D4
768C3
987D4

 

So if in column type the value is C then lookup in column ID if there is a record with the same ID, if so take the interval value of D, otherwise use the value in the column interval.

 

So the result am looking for is

IDTypeIntervalResult
123A11
234B22
345C34
345D44
768C33
987D44

in this example ID 345 has for both records the value of type D.

 

Hopefully someone knows how to write this in a DAX calculated column.

 

thanks in advanced

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

     

     

5 Replies

  • 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?

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi icturion ,

     

    You can create a column with below codE:-

    Result = 
CALCULATE (
    MAX ( 'Table (2)'[Interval] ),
    FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[ID] = EARLIER ( 'Table (2)'[ID] ) )
)

    Output:-

     

    Thanks,

    Samarth

     

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