Measure - SQL to DAX, How to use

[ifat_c]

Hi

I have an SQL query that I want to convert to DAX and I don't know how to do it. can you help me? 

in the table, I have an ID field, date field, and other fields. I want to return for every ID the line in which the date is the latest (= the most current status of the ID).

select *
from table as t
where t.date >= All (select date from table as t1 where t.id=t1.id)

this is the DAX query I tried to use when I had only one ID (it works). but once I have more than one ID it needs to be altered.

show_last_date =
VAR LatestDate =
CALCULATE ( MAX ( 'table'[date] ), ALL ( 'table' ) )
RETURN
IF ( MIN ( 'table'[date] ) = LatestDate, 1, 0 )

also, after I have a working measure, is there a way to not show it as a field in the table? (I'm using a multi-row card which I can't minimize columns in)

thanks!

[v-lili6-msft]

hi, @ifat_c 

from your measure “this is the DAX query I tried to use when I had only one ID (it works)” and your description, I think the logic is a mess, So could you share you some simple sample data and expected output.

Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,

Lin

[ifat_c]

Hi, I'll try to be more specific.

in the table, I have an ID field, date field, and other fields. I want to return for every ID the line in which the date is the latest (= the most current status of the ID).

this is the SQL query I would use to do this:

select *
from table as t
where t.date >= All (select date from table as t1 where t.id=t1.id)

I need your help to translate it to DAX to use as a measure

sample data:

ID	Date	name	status
1	01/01/2019	a	on
2	01/01/2019	b	off
3	01/02/2019	c	on
2	01/02/2019	b	on
3	01/01/2019	c	on
1	01/02/2019	a	off

desired outcome:

ID	Date	name	status
3	01/02/2019	c	on
2	01/02/2019	b	on
1	01/02/2019	a	off

[v-lili6-msft · Accepted solution]

hi, @ifat_c 

You should use ALLEXCEPT Function as below:

show_last_date = 
VAR LatestDate =
CALCULATE ( MAX ( 'table'[date] ), ALLEXCEPT('Table','Table'[ID ]))
RETURN
IF ( MIN ( 'table'[date] ) = LatestDate, 1, 0 )

And just drag the measrue into Visual level filter of the visual not into Value and set is "1"

here is pbix file, please try it.

Best Regards,

Lin

[ifat_c]

thank you so much!! it works now!

and how do you not present the show_last_date column?

[ifat_c]

never mind, got it!

just needed to remove it from "fields"

thank you!