Return Earliest Text Value to all rows of a group

[filarap]

Hi Community,

I need help with DAX through PowerPivot (not PowerBI).
I am trying to get the earliest ID of a subset(group) assigned to all rows of that group. The output I am trying to achieve is bellow *Last column - Earliest Unique ID.

Any idea how to achieve this without usage of VAR (It does not work for my Excel version)

Case ID	Rank within Group	Unique ID	??? Earliest Unique ID
1	1	1	1
1	2	2	1
1	3	3	1
2	1	4	4
2	2	5	4
3	1	6	6
4	1	7	7

[amitchandak · Accepted solution]

@filarap ,

new column  =

minx(filter(Table, [Case ID] =earlier([Case ID])), [Unique ID] )

[filarap]

Hey @Richard_100  and @amitchandak 

I managed to achieve it by combining your 2 suggestions:
=minx(filter(Table, [Case ID] =earlier([Case ID]) && [Rank within Group]=1), [Unique ID] )

Unfortunatelly SELECTCOUMN does not work in Excel PowerPivot I have.
The only thin is that I had to format Unique Value as number (last digit get converted to 0) and work around scientific format. But this resolved what I need it for.

Thanks a lot!

[Richard_100 · Accepted solution]

Hello

This worked for me, although I am not sure about backwards compatibility with Excel versions of DAX.

I changed your example table slightly to make Unique ID contain some non-numbers and then focused the solution on the rank rather than minimum Unique ID number, assuming that the rank of a Unique ID might be completely unconnected with the value of the ID itself.

This works by filtering the table down to a single row (based on Case ID, referencing the row context of the underlying table via the EARLIER function and the Rank) and a single column, thereby becoming a single cell that DAX can return as a scalar value into the row (tables of one row and column can be treated as both tables and scalars) 

=SELECTCOLUMNS(

             FILTER(Table1, ([Case ID]=EARLIER([Case ID])) && [Rank within Group]=1),

             "@Unique", [Unique ID])

Hope that helps

Regards

Richard

[filarap]

Hey @amitchandak 

Apologies as i failed to mention, that I am trying to get the Earliest ID using the rank column (Need to assign Unique ID with Rank 1 to all rows of the group).

Also, Case id is formated as text (20char, so numbers transform to scietific formating).

Thanks a lot!!!

[amitchandak · Accepted solution]

@filarap ,

new column  =

minx(filter(Table, [Case ID] =earlier([Case ID])), [Unique ID] )