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!