Forum Discussion

alya1's avatar
alya1
Helper V
2 years ago
Solved

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!

Need Help
  • samratpbi's avatar
    samratpbi
    2 years ago

    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

  • samratpbi's avatar
    samratpbi
    Super User
    2 years ago

    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's avatar
      alya1
      Helper V
      2 years ago

      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's avatar
        samratpbi
        Super User
        2 years ago

        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!

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

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