cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper II

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

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!

1 ACCEPTED SOLUTION
Solution Supplier

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!

5 REPLIES 5
Solution Supplier

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!

Helper II

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.

Solution Supplier

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!

Helper II

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

Super User

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors