Simple PowerBI Dax Problem (Select text value from table)

user_15584024 0
Viewed 133

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)

dax powerbi
1 Answers

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

enter image description here

Card Visual

enter image description here

user_56210550
Related