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

Hi gbaia 

the problem is surely here

 

SELECTEDVALUE(CloseDateTable[Date])

 

this returns probably blank unless you have that column grouped. You should substitute that expression with something like MAX (CloseDateTable[Date]) or something like this (in a card you are not filtering that column so SELECTEDVALUE returns a blank in presence of multiple values available)

 

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI