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