Forum Discussion
Measure - SQL to DAX, How to use
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!
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
5 Replies
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/38490Best Regards,
Lin
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 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
