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' which is a date table that I created which is linked to the cases table field ]date opened].

Those 2 slicers get my calculated fields which count all clients within the cases that are still open - meaning where the open date is before dateOpened slicer and closed date is after the dateClosed slicer OR blank. That works with the DAX below: 

 

TotalClient2 = CALCULATE(DISTINCTCOUNT('cases'[Client_ID]),
union(filter(all(CloseDateTable),CloseDateTable[Date]=blank()),CloseDateTable)

Then I have a card where I want to show the same as the above, but add a further filter - where the [date opened]>='dateClosed' slicer - that's to count only 'new cases' and not cases that existed before that. 

As soon as I add the futher filter, the blank closed dates get ignored. I tried many ways to do this without luck. What am I doing wrong please? This is one of the expressions I tried, but again it ignores the blank closed dates. Thank you! 

TotalNewClient2 =
CALCULATE(
    DISTINCTCOUNT('cases'[Client_ID]),
    UNION(
        SELECTCOLUMNS(
            FILTER(
                ALL(CloseDateTable),
                CloseDateTable[Date] = BLANK()
            ),
            "Client_ID", count('cases'[Client_ID])
        ),
        SELECTCOLUMNS(
            FILTER(
                'cases',
                'cases'[CaseDate] >= SELECTEDVALUE(CloseDateTable[Date])
            ),
            "Client_ID", 'tablename'[Client_ID]
        )
    )
)
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.

9 Replies

  • 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

  • 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.
  • gbaia's avatar
    gbaia
    Regular Visitor
    6 months ago

    FBergamaschi thank you. The SELECTEDVALUE(CloseDateTable[Date]) is the slicer so I can't remove the selected value and if I put MAX, I'll get the same result. It was painful to get that first DAX to work as Power BI doesn't seem to like blank values being added in additional to other filtering conditions, but it worked fine on the first one.

    Just adding that additional clause is proving impossible as it just removes the blanks!

    Thank you so much and I'll take a look at your content, thanks 🙂

  • v-tejrama's avatar
    v-tejrama
    Community Support
    6 months ago

    Hi gbaia ,

     

    Thank you Hans-Georg_Puls for the response provided!

    Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Thank you.

    • v-tejrama's avatar
      v-tejrama
      Community Support
      6 months ago

      Hi gbaia ,

       

      I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

      Thank you.