Forum Discussion

GON76's avatar
GON76
Helper III
1 year ago
Solved

Using two conditions to return a specific value

I am trying to use two conditions to return a value.  I'm using the following formula:   Compliance Status = IF(OR     ('V_ENROLLMENTS'[EXITDATE].[Date] < EDATE(TODAY(), -3), ISBLANK([EXITDATE])...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    1 year ago

    Hi,

    Write this calculated column formula

    =if(or('V_Enrollments'[Exitdate]=blank(),'V_Enrollments'[Exitdate]<=edate(today(),-36)),"Not compliant","Compliant")

    Hope this helps.

GON76's avatar
GON76
Helper III
1 year ago

Do I need to do something when I make an update to a table in order to get it to reflect on my report view?  I'm still seeing Compliant when the EXITDATE is missing.

MohamedFowzan1's avatar
MohamedFowzan1
Super User
1 year ago

Could you debug using a calculated column and confirm if the dax is actually reading these dates as blank using the below:
CheckBlank = IF(ISBLANK('V_ENROLLMENTS'[EXITDATE]), "Blank", "Not Blank")

Try this as well to confirm:
Compliance Status =
IF (
ISBLANK('V_ENROLLMENTS'[EXITDATE]),
"EXIT Date Missing",
IF(
'V_ENROLLMENTS'[EXITDATE] < DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
"Not Compliant",
"Compliant"
)
)

and proceed with

Compliance Status =
IF (
ISBLANK('V_ENROLLMENTS'[EXITDATE]), 
"Not Compliant",
IF(
'V_ENROLLMENTS'[EXITDATE] < DATE(YEAR(TODAY()) - 3, MONTH(TODAY()), DAY(TODAY())),
"Not Compliant",
"Compliant"
)
)

Incase you were able to pinpoint the issue for the blank dates and it has any other value use || and handle it along with ISBLANK
Let us know if you were able to debug the issue by any chance