topic Calculated Column that returns the first value of another column, filtered by two columns. in DAX Commands and Tips

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

Calais333 — Sun, 14 May 2023 00:21:06 GMT

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

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

Jihwan_Kim — Sun, 14 May 2023 05:10:35 GMT

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

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

Calais333 — Sun, 14 May 2023 08:15:13 GMT

Excelent, that worked perfectly. Thanks!