topic Re: Get latest date based on filtered column in DAX Commands and Tips

Get latest date based on filtered column

hadi2002 — Wed, 20 May 2020 07:30:23 GMT

Hi All, I have a dt which contain a 'Date' & 'Status' column. I just want to get the most latest date amoung those records which has 'Status'='Success' & 'Exception'. Once get the latest date, I need to count how many minutes diffrent from current time.

I tried below query, but I'm getting this error "A single value for column 'ActionDate' in table 'cndn_main' cannot be determined."

Queue = CALCULATE

(

MAX('main'[Date], FILTER('main', main[Status] IN { "Success", "Exception" }))

)

Can anyone help me to guide this please? Am I writing anything wrong?

Re: Get latest date based on filtered column

Anonymous — Wed, 20 May 2020 07:40:32 GMT

hadi2002 ,

Try adding ALL in Filter.

Queue = CALCULATE

(

MAX('main'[Date], FILTER(ALL('main'), main[Status] IN { "Success", "Exception" }))

)

Queue = CALCULATE

(

MAX('main'[Date], FILTER(ALL('main'), main[Status] ="Success" || main[Status] = "Exception" ))

)

Please share some sample data to help you better.

Regards,

Harsh Nathani

Re: Get latest date based on filtered column

hadi2002 — Thu, 21 May 2020 11:43:48 GMT

Hi Anonymous , thanks alot. That works but gaves me this error.

A single value for column 'ActionDate' in table 'cndn_main' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

This is how data look like? Could you please help me on this.

Re: Get latest date based on filtered column

Anonymous — Thu, 21 May 2020 12:21:13 GMT

hadi2002 ,

Syntax Error:

Queue = CALCULATE

(

MAX('main'[Date]), FILTER(ALL('main'), main[Status] ="Success" || main[Status] = "Exception" ))

)

Missed the bracket after MAX.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Get latest date based on filtered column

Anonymous — Thu, 21 May 2020 12:26:04 GMT

hadi2002 ,

Suppose you have the below Table6

Use the measures

Queue = CALCULATE

(

MAX(Table6[TimeString]), FILTER(ALL('Table6'),Table6[Status] ="Success" || Table6[Status] = "Postponed" )

)

Date DIff = DATEDIFF(MAX(Table6[TimeString]),[Queue],MINUTE)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Get latest date based on filtered column

hadi2002 — Thu, 21 May 2020 12:37:55 GMT

Hi Anonymous

Thanks for you help. But I just wants the most lowest one datediff which has 'Success' & 'Exception' status (ignore postponed) only shown in the card. 😞

Re: Get latest date based on filtered column

Anonymous — Thu, 21 May 2020 12:54:09 GMT

hadi2002 ,

Sorry, can you let me know what is the expected output.

You can put a filter on "Success" .

Also, just change "Postponed" to "Exception"

Queue =

CALCULATE

(

MAX(Table6[TimeString]), FILTER(ALL('Table6'),Table6[Status] ="Success" || Table6[Status] = "EXception" )

)

Date DIff = var a = DATEDIFF(MAXX(ALL(Table6),Table6[TimeString]),[Queue],MINUTE)

RETURN

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Get latest date based on filtered column

hadi2002 — Mon, 25 May 2020 05:28:24 GMT

hi Anonymous

Thanks alot for your help, that really work perfect as what was expected. However, I'm stuck at another last part. Hope you can help me on this to group the data. For example, let's say I have a table as below which shows a column called as Robot and ActionDate (as shown in first picture).

I just wants to group the robot column and populate the max date for each respective robots and shows me what is the diffrence from the time now.

Your help would be highly appreciated

Re: Get latest date based on filtered column

Anonymous — Mon, 25 May 2020 06:10:36 GMT

Hi hadi2002

Time Difference1 =

VAR _a =

CALCULATE( MAX ( 'Table'[Date/Time] ), ALLEXCEPT('Table','Table'[robot]))

RETURN

DATEDIFF (

_a,TODAY(),

DAY

)

Max_Date/Time = CALCULATE(MAX('Table'[Date/Time]), ALLEXCEPT('Table','Table'[robot]))

Pull Robot, Max_Date/Time and Time Difference1 in a table.

Regards,

Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Get latest date based on filtered column

Anonymous — Mon, 25 May 2020 06:21:51 GMT

hadi2002 ,

You can also use

Time Difference1 =

VAR _a =

CALCULATE( MAX ( 'Table'[Date/Time] ), ALLEXCEPT('Table','Table'[robot]))

RETURN

DATEDIFF (

_a,NOW(),

MINUTE

)

Re: Get latest date based on filtered column

hadi2002 — Mon, 25 May 2020 06:43:33 GMT

Hi Anonymous

But the result is showing all the data. Is it possible for that particlar measure to return only - list of robots which are having date diff which is less than 30 mins only? Please

Re: Get latest date based on filtered column

Anonymous — Mon, 25 May 2020 07:01:35 GMT

Hi hadi2002 ,

Measure 2 =

Var _dd = DATEDIFF(MAX('Table'[Date/Time]),NOW(),MINUTE)

REturn

//_dd

CALCULATE(COUNT('Table'[Robot]), FILTER('Table', _dd < 30))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)