Calculated column based on information of 3 other columns

[Anonymous]

Hello community members, 

I have the following question. 

I need a calculated column that provides me the  following information. 

Each unique client (UNIEKE_CLIENTCODE) can have 2 or more different 'SegmentNew' codes. Now I would like to have a calculated column that states in each row the SegmentNew code for the client that has the largest value in the column Year. (I tried to visualize the column I need in the table below). 

Can someone help me with this issue? 

Hope to hear soon from you! 

Best regards, Sander

UNIEKE_CLIENTCODE	Age	Year	SegmentNew	Calculated column I need
Ax65	< 75	0,91	1A Begeleiding	2A
Ax65	< 75	1,5	2A	2A
Ax11	< 75	2,56	1A Begeleiding	1A Begeleiding
Ax11	< 75	1,27	2A	1A Begeleiding
AxF5	< 75	0,66	1A Begeleiding	1B
AxF5	< 75	2,24	1B	1B

[DataInsights]

@Anonymous,

Try this calculated column:

SegmentNew Max Year = 
VAR vMaxYear =
    CALCULATE (
        MAX ( Table1[Year] ),
        ALLEXCEPT ( Table1, Table1[UNIEKE_CLIENTCODE] )
    )
VAR vResult =
    CALCULATE (
        MAX ( Table1[SegmentNew] ),
        ALLEXCEPT ( Table1, Table1[UNIEKE_CLIENTCODE] ),
        Table1[Year] = vMaxYear
    )
RETURN
    vResult

[Anonymous]

Hi @DataInsights , 

This calculated column works perfect!

Just one additional question. In the column SegmentNew, there is one particular code (3A) that should be excluded in the formula (and the results in the calculated formula). In other words, if a unique client has a Segment New, 2A and 3A (and the 3A has the max year), the result should be 2A...  

Easy to insert this in the formula? 

Best regards, Sander

[DataInsights · Accepted solution]

@Anonymous,

Try this:

SegmentNew Max Year = 
// if a client has a SegmentNew, 2A and 3A (and the 3A has the max year), the result should be 2A
VAR vMaxYear =
    CALCULATE (
        MAX ( Table1[Year] ),
        ALLEXCEPT ( Table1, Table1[UNIEKE_CLIENTCODE] )
    )
VAR vSegmentNew =
    CALCULATE (
        MAX ( Table1[SegmentNew] ),
        ALLEXCEPT ( Table1, Table1[UNIEKE_CLIENTCODE] ),
        Table1[Year] = vMaxYear
    )
VAR vRowCount2A =
    CALCULATE (
        COUNTROWS ( Table1 ),
        ALLEXCEPT ( Table1, Table1[UNIEKE_CLIENTCODE] ),
        Table1[SegmentNew] = "2A"
    )
VAR vResult =
    IF (
        NOT ISBLANK ( vSegmentNew )
            && vRowCount2A > 0
            && vSegmentNew = "3A",
        "2A",
        vSegmentNew
    )
RETURN
    vResult

[Anonymous]

@DataInsights thanks for your help!