Forum Discussion
Setting a filter with a variable first, then calculating a measure with ISBLANK
I'm not using a date table and calculating a measure, which sometimes has blank values. The dates I'm looking at are from 7/1/202 to 6/30/2023.
I used an "ISBLANK" to calculate my measure and add 0s if the data is blank. This worked, but gave me zeros from Jan-Jun 2020, and from Jul-Dec 2023. When plotted on the line chart, this caused the line to start at Jan 2020 and go through Dec 2023.
I thought of using a VAR to set my filtered dates first, then look at the measure. Could I do something like this:
**************
I think the VAR is correct and the IF(ISBLANK) is correct, but what does the DAX look like if I want to evaluate a measure row by row for only these dates? If the data is blank, I'll put a 0 in. If not, I'll use the measure's answer.
Thanks!
2 Replies
- amitchandak
Super User
RJSuttlemyre , Try like
0 between range
Measure = var _1= [Your Measure]+0
var _min = minx(ALLSELECTED('Calendar'), 'Calendar'[Date])
var _max = maxx(ALLSELECTED('Calendar'), 'Calendar'[Date])
return
CALCULATE(if(max('Calendar'[Date]) <_min || max('Calendar'[Date]) >_max , BLANK(), _1))Thanks so much.
I kind of used our approach, but took it a little different way.
The event I'm trying to count is Event 8000. When I countrows in that measure, I add a zero so in case the data is missing (the event doesn't always happen) it adds a zero so I can graph it.
I used a VAR approach similar to yours, but modified it a bit. However, I'm still getting zeros before and after the date period.
Can you please look at the DAX below and let me know if you see an issue?
Count of 8000 v3 =VAR StartDate = DATE(2020, 7, 1)VAR EndDate = DATE(2023, 6, 30)RETURNCALCULATE(IF(MIN(Events[Date].[Date]) >= StartDate &&MAX(Events[Date].[Date]) <= EndDate,BLANK(),[Count of 8000]))
