Issues when evaluating records for three conditions, include a date element

[ttoastt]

I've got a data set showing equipment serial number and a variety of statuses as a user works to update the software. Starts with consent, then download pending, download started, download finishes and onto flash statuses, assuming everything works well.

There are a lot of failures in the process, but many of them are examples of a machine not being turned on within seven days of the user starting the download process. The download times out and has to be restarted if it doesn't start within that period.

My current approach was to create a calculated column

• If the row has a flash status of DOWNLOAD_FAILED, then evaluate the following

• Does the machine have a download_pending record that was seven days prior to the download_failed record

• If so, mark it "download expired"

Download_Expire = IF(MV_RT_FL_MONTHLY[Flash Status Description] <> "DOWNLOAD_FAILED", "",
IF( COUNTROWS(FILTER(
MV_RT_FL_MONTHLY, 
MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] = MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] &&
MV_RT_FL_MONTHLY[Flash Status Description] = "DOWNLOAD_PENDING" &&
MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] = DATEADD(DateTable[Date].[Date], -7, DAY)))
> 0, "DOWNLOAD EXPIRED", "")
)

This is returning nothing, so I tried a different way using variables and can get it to return the count of rows for the machine that are 'download_pending', but I'm strugging to get the date element taken into account. Any input would be awesome!

[Anonymous · Accepted solution]

Here's the right solution:

Download_Expire =
var __currentStatus = MV_RT_FL_MONTHLY[Flash Status Description]
var __serialNumber = MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER]
var __currentDate = MV_RT_FL_MONTHLY[Flash_timestamp]
return
IF (
    __currentStatus = "DOWNLOAD_FAILED"
    &&
    NOT ISEMPTY(
        FILTER (
            MV_RT_FL_MONTHLY,
            MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] = __serialNumber
            &&
            MV_RT_FL_MONTHLY[Flash Status Description] = "DOWNLOAD_PENDING"
            && 
            MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] = __currentDate - 7
        )
    ),
    
    "DOWNLOAD EXPIRED"
)

Best

D

[Anonymous · Accepted solution]

Here's the right solution:

Download_Expire =
var __currentStatus = MV_RT_FL_MONTHLY[Flash Status Description]
var __serialNumber = MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER]
var __currentDate = MV_RT_FL_MONTHLY[Flash_timestamp]
return
IF (
    __currentStatus = "DOWNLOAD_FAILED"
    &&
    NOT ISEMPTY(
        FILTER (
            MV_RT_FL_MONTHLY,
            MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] = __serialNumber
            &&
            MV_RT_FL_MONTHLY[Flash Status Description] = "DOWNLOAD_PENDING"
            && 
            MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] = __currentDate - 7
        )
    ),
    
    "DOWNLOAD EXPIRED"
)

Best

D

[ttoastt]

I tried this and it's returning blanks, so I removed the date condition within the filter and now it does return 'download_expired' for every row that has a status of 'download_failed'. It looks like the date comparison is what is causing issues. 

[Anonymous · Accepted solution]

If timestamp is a datetime and not a date, then it certainly causes issues. If you have a datetime, you have to do it in a different way. You have to have a column with just dates, no time involved.

Then the formula will work.

Best
D

[ttoastt]

Thank you, this set me down the correct path. Retyped it and was prompted to specify .[Date] after the date column and that is yielding the desired output. Thank you!

[az38]

Hi @ttoastt 

its difficult to answer without data example but

1. Very strange condition for me 🙂

MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER] = MV_RT_FL_MONTHLY[EQUIPMENT_SERIAL_NUMBER]

 2. Not sure your date statement MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] = DATEADD(DateTable[Date].[Date], -7, DAY) works correct

try

Download_Expire = 
var _cntRows = CALCULATE(COUNTROWS(MV_RT_FL_MONTHLY), 
FILTER(ALL(MV_RT_FL_MONTHLY), 
MV_RT_FL_MONTHLY[Flash Status Description] = "DOWNLOAD_PENDING" &&
MV_RT_FL_MONTHLY[FLASH_TIMESTAMP] >= DATEADD(DateTable[Date], -7, DAY)
)
)

RETURN

IF(
MV_RT_FL_MONTHLY[Flash Status Description] <> "DOWNLOAD_FAILED", 
"",
IF( _cntRows > 0, "DOWNLOAD EXPIRED", "")
)