Calculated Column that returns the first value of another column, filtered by two columns.

[Calais333]

Hi,

I need a calculated column that returns the first value from another column that is filtered by two additional columns. In excel I use the IF function. The table is a sample of what I am trying to achieve, the calculated column is "Result". 

ID	Label	Unit	Result
1000	X1	1.5	1.5
1000	X1	1.5
1000	X1	1.5
1000	B2	2	2
1000	B2	2
1000	C3	1.8	1.8
1000	C3	1.8
1000	C3	1.8
1005	X1	1.9	1.9
1005	X1	1.9
1005	Z1	2.1	2.1
1005	Z1	2.1
1005	Z1	2.1
1005	Z2	3	3
1005	Z2	3
1005	C3	1.6	1.6
1008	C3	1.6
1008	C3	1.6
1008	X1	2	2

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I assume you have another column in the table that indicates which one is the first row in the group, for instance, a date column or an index column.

Please check the below picture and the attached pbix file.

Result CC = 
VAR _reference =
    CALCULATE (
        MAX ( Data[Index] ),
        INDEX (
            1,
            SUMMARIZE ( ALL ( Data ), Data[ID], Data[Label], Data[Unit], Data[Index] ),
            ORDERBY ( Data[Index], ASC ),
            KEEP,
            PARTITIONBY ( Data[ID], Data[Label] )
        )
    )
RETURN
    IF ( Data[Index] = _reference, Data[Unit] )

[Calais333]

Excelent, that worked perfectly. Thanks!