Return column name if data matches another column data in same row (or MIN value- select column)

[alya1]

Hello,

I have table such as below: 

Start Date	Date 1	Date 2	Date 3	Date 4	Date 5	(DAX new column) Earliest Date
1/1/20			1/3/23	1/3/23		1/3/23
5/5/20		9/9/23			5/20/24	9/9/23

with many more rows.
Please note (DAX new column) Earliest Date only looks at Date 1 - Date 5 columns (Start Date don't count).

How can I return the column name (Date 1 - Date 5), that contains value in (DAX new column) Earliest Date? 
Please note some may return multiple column name. 

Goal:

Start Date	Date 1	Date 2	Date 3	Date 4	Date 5	(DAX new column) Earliest Date	(DAX new column) Earliest Date Column
1/1/20			1/3/23	1/3/23		1/3/23	Date 3, Date 4
5/5/20		9/9/23			5/20/24	9/9/23	Date 2

Thank you so much!

[samratpbi]

Hi,
considering you have 5 columns to compare, you can create below mentioned measures to achieve your goal.

First measure to find min date:

Min_Date =

VAR dt_tab =

{

    MIN(Earliest_Date[Date 1 ]),

    MIN(Earliest_Date[Date 2 ]),

    MIN(Earliest_Date[Date 3 ]),

    MIN(Earliest_Date[Date 4 ]),

    MIN(Earliest_Date[Date 5 ])

}

VAR _minDt =

MINX(dt_tab,[Value])

RETURN

    _minDt

 Next to find which all columns have minimum date:
 Min_Date_Col =

VAR _x = ""

VAR _dt1 =

IF([Min_Date] = VALUES(Earliest_Date[Date 1 ]), "Date1, ")

VAR _dt2 =

IF([Min_Date] = VALUES(Earliest_Date[Date 2 ]), "Date2, ")

VAR _dt3 =

IF([Min_Date] = VALUES(Earliest_Date[Date 3 ]), "Date3, ")

VAR _dt4 =

IF([Min_Date] = VALUES(Earliest_Date[Date 4 ]), "Date4, ")

VAR _dt5 =

IF([Min_Date] = VALUES(Earliest_Date[Date 5 ]), "Date5, ")

VAR _returnText =

_dt1 & _dt2 & _dt3 & _dt4 & _dt5

RETURN

LEFT(_returnText,LEN(_returnText)-2)

 Below is the result:

If this resolves your problem, then please accept this as solution, Thanks!

[alya1]

Thank you so much for writing everything out! May I ask if you know how to fix the error "A table of multiple values was supplied where a single value was expected." please? I do have a few rows where there is no date listed in any columns. 

[samratpbi · Accepted solution]

Hi,
I am not sure when you are getting this error, however I have added a row with all the Date 1 - Date 5 as blank and modified the 2nd measure a little to show the value:

Min_Date_Col =

VAR _x = ""

VAR _dt1 =

IF([Min_Date] = VALUES(Earliest_Date[Date 1 ]), "Date1, ")

VAR _dt2 =

IF([Min_Date] = VALUES(Earliest_Date[Date 2 ]), "Date2, ")

VAR _dt3 =

IF([Min_Date] = VALUES(Earliest_Date[Date 3 ]), "Date3, ")

VAR _dt4 =

IF([Min_Date] = VALUES(Earliest_Date[Date 4 ]), "Date4, ")

VAR _dt5 =

IF([Min_Date] = VALUES(Earliest_Date[Date 5 ]), "Date5, ")

VAR _returnText =

_dt1 & _dt2 & _dt3 & _dt4 & _dt5

RETURN

IF(

    ISBLANK([Min_Date]),

    "",

    LEFT(_returnText,LEN(_returnText)-2)

)

If this resolves your problem, then please accept this as solution, Thanks!

[alya1]

Hello, thank you for the update! I did a few treaks on your DAX and it came out beautifully! much appreciated samratpbi!

[lbendlin]

You need to unpivot your data to bring it into a usable format. After that the measures will write themselves.