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 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?
  • 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)

11 Replies

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