Simple PowerBI Dax Problem (Select text value from table)

Question

Dummy example below explaining my issue:

Data:

Methods of travel

| Recency | Method |..... Date ....|
|...1...........|...Car.....|2021-10-01|
|...2...........|..Lorry....|2021-09-01|
|...3...........|..Bike.....|2021-08-01|

I am trying to create a measure which selects the most recent method of travel.

E.g. in SQL:

Select method From Table where recency = (select min(recency) from table)

Two things i have tried so far:

Calculate(max(Table[method])Filter(Table,Table[Recency] = [Min_Recency]))

And

Lookupvalue(Table[Method],Table[Recency],[Min_Recency])

Where [Min_Recency] gives back the min(Recency)

It will depend on the filter context, what sort of output are you trying to achieve? can you share a tabular output as an example? — Angelo Canepa
Just need a card that states the method of travel that was most recent. E.g. If i put in a slicer and filter on dates for a max date of 2021-09-02, then the output in the card will be 'Lorry'. To get the minimum recency (in my example there are multiple rows with the same date) I used the following: Min_Recency= calculate(min(table[recency]),filter(table[date]=max(table[date]))) I feel like i should be able to then say Calculate(table[Method]),Filter(table[Recency])=[Min_Recency] But after 'calculate(' there needs to be a syntax that works. — Nennyd

Angelo Canepa Angelo Canepa · Accepted Answer · 2021-04-09T11:01:22

I have expanded your table to make it easier to explain.

TABLE

Recency	Method	Date
1	Car	2021-10-01
2	Lorry	2021-09-01
3	Bike	2021-09-01
4	Bike	2021-08-01

Calculation: Measure

MostRecentMethod =
VAR SelectedDate =
    SELECTEDVALUE ( 'Table'[Date] )
VAR MinRecency =
    CALCULATE ( MIN ( 'Table'[Recency] ), 'Table'[Date] = SelectedDate )
RETURN
    CALCULATE ( SELECTEDVALUE ( 'Table'[Method] ), 'Table'[Recency] = MinRecency )

Output

Table Visual

Card Visual

Simple PowerBI Dax Problem (Select text value from table)

1 Answers

Calculation: Measure

Output