Forum Discussion

gbaia's avatar
gbaia
Regular Visitor
6 months ago
Solved

DAX expression ignoring blanks statement when extra filter is added

Hi there, I'm struggling with this... I have 2 slicers, 'dateClosed' which is a date table that I created which is linked to the cases table field [date closed]. And another slicer 'dateOpened' wh...
Need Help
  • Hans-Georg_Puls's avatar
    Hans-Georg_Puls
    6 months ago

    Hi gbaia ,

    if you need the relationships for some other reasons, try the following:

    • Remove all filters from your two date dimensions in both measures
    • Change the check for blank values a little bit (EDIT: Maybe it was a bit late yesterday evening, today the solution works without this extra change. So give it a try with just removing the filters...)

    The result are the following two measures:

    TotalClient2 =
    VAR _MaxOpenedDate = MAX(OpenDateTable[Date])
    VAR _MinClosedDate = MIN(CloseDateTable[Date])
    VAR _result = CALCULATE(
        DISTINCTCOUNT('cases'[Client_ID]),
        REMOVEFILTERS(CloseDateTable), REMOVEFILTERS(OpenDateTable),
        ('cases'[date closed] > _MinClosedDate || ISBLANK('cases'[date closed]))
        && ('cases'[date opened] <= _MaxOpenedDate)
    )
    RETURN
        _result
     
    TotalNewClient2 =
    VAR _MaxOpenedDate = MAX(OpenDateTable[Date])
    VAR _MinClosedDate = MIN(CloseDateTable[Date])
    VAR _result = CALCULATE(
        DISTINCTCOUNT('cases'[Client_ID]),
        REMOVEFILTERS(CloseDateTable), REMOVEFILTERS(OpenDateTable),
        ('cases'[date closed] > _MinClosedDate) || (NOT(ISDATETIME( 'cases'[date closed])))
        && ('cases'[date opened] <= _MaxOpenedDate)
        && ('cases'[date opened] >= _MinClosedDate)
    )
    RETURN
        _result
     
    To be honest I can not explain instantly why the second change is necessary but it seems to work.
     
    Hope that this is a working solution for your issue.
gbaia's avatar
gbaia
Regular Visitor
6 months ago

Hans-Georg_Puls thank you so much, that works beautifully without the relationships. But I can't really delete the relationships on my file or it'll break everything else! It's just that card which needs the extra cause.
I've changed the DAX to your DAX and it's better - at least it takes into consideration the last condition which mine didn't. But it still ignores the cases where closed date is blank 😞
Can I get that to work still having the relationship?

Thank you so much!

Hans-Georg_Puls's avatar
Hans-Georg_Puls
Super User
6 months ago

Hi gbaia ,

if you need the relationships for some other reasons, try the following:

  • Remove all filters from your two date dimensions in both measures
  • Change the check for blank values a little bit (EDIT: Maybe it was a bit late yesterday evening, today the solution works without this extra change. So give it a try with just removing the filters...)

The result are the following two measures:

TotalClient2 =
VAR _MaxOpenedDate = MAX(OpenDateTable[Date])
VAR _MinClosedDate = MIN(CloseDateTable[Date])
VAR _result = CALCULATE(
    DISTINCTCOUNT('cases'[Client_ID]),
    REMOVEFILTERS(CloseDateTable), REMOVEFILTERS(OpenDateTable),
    ('cases'[date closed] > _MinClosedDate || ISBLANK('cases'[date closed]))
    && ('cases'[date opened] <= _MaxOpenedDate)
)
RETURN
    _result
 
TotalNewClient2 =
VAR _MaxOpenedDate = MAX(OpenDateTable[Date])
VAR _MinClosedDate = MIN(CloseDateTable[Date])
VAR _result = CALCULATE(
    DISTINCTCOUNT('cases'[Client_ID]),
    REMOVEFILTERS(CloseDateTable), REMOVEFILTERS(OpenDateTable),
    ('cases'[date closed] > _MinClosedDate) || (NOT(ISDATETIME( 'cases'[date closed])))
    && ('cases'[date opened] <= _MaxOpenedDate)
    && ('cases'[date opened] >= _MinClosedDate)
)
RETURN
    _result
 
To be honest I can not explain instantly why the second change is necessary but it seems to work.
 
Hope that this is a working solution for your issue.