Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

get latest Value in Direct Query

Hello all,

 

I am using direct query. See attached sample data.

 

I want to show latest entries for each application, and its status (Is_error) and Actual response.

It shows correctly as below when I use only App Id and 'Latest dateTime'  (

Latest DateTime = Calculate(Max( Table1[Request_TimeStamp​]))  in a table visual.

 

But as I add other fields like Is_Error, actual response , it shows both entries ( Is_error =0 and 1). I need to show only latest entry against the app id.

 

Sample data -

App_Id​Request_Date​Request_TimeStamp​Response_TimeStamp​Is_Error​Actual_Response​
112/1/2012:21:18 PM12:21:25 PM0Application is up
112/1/2012:21:14 PM12:21:23 PM0Application is up
112/1/2012:20:53 PM12:21:01 PM0Application is up
212/1/2012:22:23 PM12:22:33 PM0Application is up
212/1/2012:22:30 PM12:22:39 PM1Fail to connect URL 
212/1/2012:21:55 PM12:22:07 PM0Application is up
312/1/2012:22:10 PM12:22:21 PM0Application is up
312/1/2012:21:22 PM12:21:32 PM0Application is up
312/1/2012:22:33 PM12:22:48 PM0Application is up
412/1/2012:21:58 PM12:22:08 PM1Fail to connect URL
412/1/2012:21:07 PM12:21:21 PM0Application is up
512/1/2012:20:55 PM12:21:01 PM0Application is up
512/1/2012:22:11 PM12:22:21 PM1Fail to connect URL 

Thank You!

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    Not sure if you are adding a calculated column or a measure.  I would do it as a measure and not have a relationship to your icon table, using a measure like this.

     

    Icon =
    VAR thisstatus = [Status_Error]
    RETURN
        CALCULATE (
            MIN ( Icon[IconURLColumn] ),
            FILTER (
                VALUES ( Icon[Icon_Key] ),
                Icon[Icon_Key] = thisstatus
            )
        )

     

     

    Regards,

    Pat

4 Replies

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Please try a measure like this instead of adding the Is_Error column, replacing table and column names as needed.

     

    Status_Error = LASTNONBLANKVALUE(Table[ResponseTimeStamp]), MIN(Table[Is_Error]))

     

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks mahoneypat  for the help. it worked perfectly.

       

      Now adding Up/ Down arrow in the same table visual istead of above one.

      So created new column as - 

      KPI Ind = If(Table[Is_Error​]=0,"1","2") which is linked to new table ICON column and  ICON_Key (1- Up & 2- Down)
      then again it showed both (0, 1) values of status_error field, as below. 

       

      UP/Down icon need to show against latest status_error. Any pointers will be helpful.

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Not sure if you are adding a calculated column or a measure.  I would do it as a measure and not have a relationship to your icon table, using a measure like this.

     

    Icon =
    VAR thisstatus = [Status_Error]
    RETURN
        CALCULATE (
            MIN ( Icon[IconURLColumn] ),
            FILTER (
                VALUES ( Icon[Icon_Key] ),
                Icon[Icon_Key] = thisstatus
            )
        )

     

     

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Based on your description, you can create a measure as follows.

     

    Latest DateTime = MAX('Table1'[Request_Date​])&" "&MAXX(FILTER(ALL('Table1'),[App_Id​]=SELECTEDVALUE('Table1'[App_Id​])),Table1[Request_TimeStamp​])

     

    Result:

     

    Hope that's what you were looking for.

    Best Regards,

    Yuna

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.