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.
Hans-Georg_Puls's avatar
Hans-Georg_Puls
Super User
6 months ago

Hi gbaia ,

if I understand your requirements right and assuming linking means that there is a relationship between the tables, I would recommend a slightly different approach based on the following three steps:

  • Disconnect the two date tables from the fact table
  • Decide inside your measures what filters what
  • Use Variables

That leads to much easier to understand measures and incidentally to a much better performance avoiding ALL and FILTER.

 

I built a little demo for you that you find attached. 

 

My measures look like this (where possible I used your notation and names):

TotalClient2 =
VAR _MaxOpenedDate = MAX(OpenDateTable[Date])
VAR _MinClosedDate = MIN(CloseDateTable[Date])
VAR _result = CALCULATE(
    DISTINCTCOUNT('cases'[Client_ID]),
    ('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]),
    ('cases'[date closed] > _MinClosedDate || ISBLANK('cases'[date closed]))
    && ('cases'[date opened] <= _MaxOpenedDate)
    && ('cases'[date opened] >= _MinClosedDate)
)
RETURN
    _result
 
The result looks good to me but of course I'm not sure if I met all of your requirements or your requirements at all.
 
Hope that helps!
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.