Forum Discussion
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...
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_resultTotalNewClient2 =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_resultTo 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.
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.