Using FILTER to Return Non-Numeric Value

[Anonymous]

I'm looking for the DAX function that behaves something like INDEX MATCH in Excel. I've used CALCULATE and FILTER to SUM or COUNT values, but this time I'm just needing to return a non-numeric value.  What function would do that?

My formula looks something like this:

=CALCULATE (

        Table1[Coach]),

        FILTER (

                Table1,

                Table1[Name]=Table2[Rep]

                && Table1[Record_Start_Date]<=Table2[WeekEndDate]

                    )

                    ) 

What am I missing? 🙂

[Zubair_Muhammad · Accepted solution]

@Anonymous 

If there can be many coaches that meet the criterion, we can concatenate their names using

=
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( Table1[Coach] ),
        FILTER (
            Table1,
            Table1[Name] = Table2[Rep]
                && Table1[Record_Start_Date] <= Table2[WeekEndDate]
        )
    ),
    [Coach],
    ","
)

[Zubair_Muhammad]

@Anonymous 

You can use MAX, MIN, VALUES, FIRSTNONBLANK, LASTNONBLANK

=
CALCULATE (
    MAX ( Table1[Coach] ),
    FILTER (
        Table1,
        Table1[Name] = Table2[Rep]
            && Table1[Record_Start_Date] <= Table2[WeekEndDate]
    )
)

[Anonymous]

That worked!  Thanks so much, Zubair