Forum Discussion

ttoastt's avatar
ttoastt
Regular Visitor
6 years ago
Solved

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

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!

Calculatetable
Date
dates
DAX
Filter
  • Anonymous's avatar
    Anonymous
    6 years ago

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

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    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", "")
)

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      ttoastt
      Regular Visitor
      6 years ago

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