Forum Discussion

hadi2002's avatar
hadi2002
Frequent Visitor
6 years ago
Solved

Get latest date based on filtered column

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...
  • Anonymous's avatar
    Anonymous
    6 years ago

    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)

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
hadi2002
Frequent Visitor
6 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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)
     
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        hadi2002
        Frequent Visitor
        6 years ago

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