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
– Angelo Canepa, Commented Apr 8, 2021 at 16:51
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
– Nennyd, Commented Apr 8, 2021 at 20:08

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

1

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

answered Apr 9, 2021 at 11:01

Angelo Canepa

1,8062 gold badges15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Simple PowerBI Dax Problem (Select text value from table)

1 Answer 1

Calculation: Measure

Output

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Calculation: Measure

Output

Comments

Your Answer

Sign up or log in

Post as a guest

Related