Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
alya1
Helper IV
Helper IV

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/231/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/231/3/23 1/3/23Date 3, Date 4
5/5/20 9/9/23  5/20/24 9/9/23Date 2

Thank you so much!

1 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)
)
samratpbi_0-1713310020807.png

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

View solution in original post

5 REPLIES 5
samratpbi
Super User
Super User

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:
samratpbi_0-1713307301598.png

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

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. 

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)
)
samratpbi_0-1713310020807.png

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

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

lbendlin
Super User
Super User

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

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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