concact and Firstsortedvalue

[BI_Dev]

Hi - How to use concat and Firsortvalue in expression ? Want to get the date and type based on the min date for each ID.

Ex: Type 

ID	Type	Date
1	Start	02/22/22
1	Process	02/22/22
1	End	03/01/22
2	Start	02/23/22
2	End	03/02/22

Here for ID 1 02/22 is min date and since Start and End has the same date I want to Concat both Types and dateS.

Output would be as below

1	Start - 02/22/22 & Process - 02/22/22
2	Start - 02/23/22

[anthonyj]

Hi,

I'm not sure of the shape of your data but if you want to display the 'Start' and 'Process' types in one field with the relevant dates then you can try something like this:

Add ID as your dimension

Then the below as a measure

=only({$<Type={Start}>}Type) & '-' & date(min({$<Type={Start}>}Date)) &

if (len(only({$<Type={Process}>}Type)) > 0,
  ' & ' & only({$<Type={Process}>}Type) & '-' & date(min({$<Type={Process}>}Date)))

I hope this helps.

Thanks

Anthony

[tresesco · Accepted solution]

Like this?

FirstSortedValue(DISTINCT Aggr( Concat ( Type & '-' & Date, '&'), ID, Date), Date)

[BI_Dev]

Awesome