I need help with a timing issue related to visuals

[rsypher12477]

I have a report that is connected to a realtime dataset in dynamoDB reporting metrics realted to calls coming in through AWS Connect. This report is running inside a premium workspace and is set to automatically refresh the visuals on the page every 15 seconds. I am using a CData connector to run a direct query against and I have a card visual called "Longest Wait" which is designed to show the max value of the longest queued time shown in the table coming from DynamoDB. I have run the performance analyzer and found that this card visual refreshes on average between 1.106 & 0.688 seconds. I am also showing this same measure "Longest Wait" in a table which provides more detail to the user like the client that is related to the longest wait and per the timings record in performance analyzer it is refreshing between 3.151 & 2.400 seconds. My question is since this report is using direct query and the 2 visuals take different amounts of time to refresh how can I make sure that I am getting the same results from the "Longest Wait" measure used in 2 different visuals?

Here is the DAX that is running the "Longest Wait" measure:

Longest Wait = 

VAR _longestQueueTime = 
CALCULATE(MAX(Connect_Realtime_Customer_Summary[Longest_Queue_Time]))

VAR Longest_Queue = 
CALCULATE(
    MAX(Connect_Realtime_Customer_Summary[Longest_Queue_Formatted])
    ,FILTER(
        Connect_Realtime_Customer_Summary
        ,Connect_Realtime_Customer_Summary[id] <> "dummy" 
        &&Connect_Realtime_Customer_Summary[id] <> "dummy2" 
        && Connect_Realtime_Customer_Summary[Longest_Queue_Time] = _longestQueueTime
    )
)  
// Gets the max value from the Longest Queue Formatted column where the id of the row is not equal to "dummy"
// calculate(max(Connect_Realtime_Customer_Summary[Longest_Queue_Formatted]),filter(Connect_Realtime_Customer_Summary,Connect_Realtime_Customer_Summary[Longest_Queue_Time] = _longestQueueTime)) 

VAR UTC_Time = 
    UTCNOW()
// Gets the current timestamp in UTC

VAR Duration = 
    DATEDIFF(Longest_Queue,UTC_Time,SECOND)
// Calculates the number of seconds elpased between the Longest Queue and UTC

VAR Hours =
    CALCULATE( INT ( Duration / 3600) )
// Calculates the number of hours, there are 3,600 seconds in an hour

VAR Minutes =
    CALCULATE( INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) )
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 

VAR Seconds =
    CALCULATE( ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) ) // We round up here to get a whole number
// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros

VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        CONCATENATE ( "", Hours )
      )
// Minutes with leading zeros

VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        CONCATENATE ( "", Minutes )
    )
// Seconds with leading zeros

VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        CONCATENATE ( "", Seconds )
    )


RETURN
   IF(
       Duration <= -1
       , "00:00:00"
        ,IF(
            Longest_Queue = "00:00:00"
            , Longest_Queue
            , CONCATENATE (
                    H,
                    CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) )
                )
        )
   )

[lbendlin]

You can't.  Visuals refresh in batches of 5 .  Your only remedy would be to have 5 or fewer visuals on your page, but even then there is no absolute guarantee that the queries for each of the visuals will run exactly in parallel.

[rsypher12477 · Accepted solution]

Thanks @lbendlin  but it is not possible to reduce the amount of visuals on the page. However, I did figure out that there is a way to get the singel max value by using 2 seperate measures and using the MAXX function.

Here is the may I am now making the calculation and it is returning better results regardless of any potential timing issue.

Longest Wait 2 = 

VAR Longest_Queue = 
CALCULATE(MAX(Connect_Realtime_Customer_Summary[Longest_Queue_Formatted]))

VAR UTC_Time = 
UTCNOW()
// Gets the current timestamp in UTC

VAR Duration = 
IF (DATEDIFF(Longest_Queue,UTC_Time,SECOND) > 0, DATEDIFF(Longest_Queue,UTC_Time,SECOND), 0)
// Calculates the number of seconds elpased between the Longest Queue and UTC

VAR Hours =
    CALCULATE( INT ( Duration / 3600) )
// Calculates the number of hours, there are 3,600 seconds in an hour

VAR Minutes =
    CALCULATE( INT ( MOD( Duration - ( Hours * 3600 ),3600 ) / 60) )
// Remaining seconds are the remainder of the seconds divided by 60 after subtracting out the hours 

VAR Seconds =
    CALCULATE( ROUNDUP(MOD ( MOD( Duration - ( Hours * 3600 ),3600 ), 60 ),0) ) // We round up here to get a whole number

// These intermediate variables ensure that we have leading zero's concatenated onto single digits
// Hours with leading zeros
VAR H =
    IF ( LEN ( Hours ) = 1, 
        CONCATENATE ( "0", Hours ),
        Hours
    )

// Minutes with leading zeros
VAR M =
    IF (
        LEN ( Minutes ) = 1,
        CONCATENATE ( "0", Minutes ),
        Minutes
    )

// Seconds with leading zeros
VAR S =
    IF (
        LEN ( Seconds ) = 1,
        CONCATENATE ( "0", Seconds ),
        Seconds
    )

// Now return hours, minutes and seconds with leading zeros in the proper format "hh:mm:ss" where the value of Longest_Queue is not "00:00:00", otherwise it will return "00:00:00"
RETURN

   IF(
       Longest_Queue = "00:00:00"
       , Longest_Queue
       , CONCATENATE (H, CONCATENATE ( ":", CONCATENATE ( M, CONCATENATE ( ":", S ) ) ) )
   )



MAX Longest Wait = 

MAXX(
    Connect_Realtime_Customer_Summary, [Longest Wait 2]
)