calculated column based on row values

[icturion]

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
123	A	1
234	B	2
345	C	3
345	D	4
768	C	3
987	D	4

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

ID	Type	Interval	Result
123	A	1	1
234	B	2	2
345	C	3	4
345	D	4	4
768	C	3	3
987	D	4	4

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

[Samarth_18]

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]

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 · Accepted solution]

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

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

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?