Get latest date based on filtered column

[hadi2002]

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?

[harshnathani]

@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

[hadi2002]

Hi @harshnathani , 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.

[harshnathani]

@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)

[harshnathani]

@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)

[hadi2002]

Hi @harshnathani 

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. 😞

[harshnathani · Accepted solution]

@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

a

Regards,
Harsh Nathani

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

[hadi2002]

hi @harshnathani 

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

[harshnathani]

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)

[hadi2002]

Hi @harshnathani 

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 

[harshnathani]

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)

[harshnathani]

@hadi2002 ,

You can also use

Time Difference1 =

VAR _a =

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

RETURN

DATEDIFF (

_a,NOW(),

MINUTE

)