DAX expression ignoring blanks statement when extra filter is added

[gbaia]

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]

        )

    )

)

[v-tejrama]

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]

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.

[Ashish_Mathur]

Hi,

Please share some data to work with and show the expected result.

[Hans-Georg_Puls]

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]

@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 · Accepted solution]

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]

@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 🙂

[FBergamaschi]

Hi @gbaia there is surely a solution, if you can pass me the pbix file I can look into that

via private message in case you cannot share it here

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

[FBergamaschi]

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