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

      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!