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

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

Reply
alya1
Helper II
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/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
Solution Supplier
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:
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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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