Forum Discussion
Solved
DAX help
I need convert the following sql to DAX. Basically I need count distinct taskID, which is created before 20180101 with In progress and not started status. select count(distinct (taskID)), count(*...
this is what you need
taskBegDay3 = VAR _selected = SELECTEDVALUE('Date'[Date (bins)],0) return CALCULATE ( DISTINCTCOUNT(Task[TaskID]) ,filter(TaskStatus,TaskStatus[TaskStatus] in {"In Progress","Not Started"}) ,FILTER(ALL(Task), _selected > Task[createddate]) )
Ok, i think i understand what you want now,
you have a slicer that you are going to select a month , and you want to see what is previous to that month, so you were closer to the answer then you think i guess.
we need a little workaround
VAR _minDate =
CALCULATE(
MIN(date[Date])
,FILTER(date
,date[date (bins)]=SELECTEDVALUE(date[Date (bins)])
)
)
return TaskBegDay = CALCULATE ( DISTINCTCOUNT(Task[TaskID]) ,filter(TaskStatus, TaskStatus[TaskStatus] in {"In Progress","Not Started"} ) ,filter(ALL(date),_minDate > Task[CreatedDate]) ) )
just for a testing scenario, copy the _minDate var and place it in a new measure, see if its showing the correct first day of month.
i cant test atm, but i believe this is at least very close to your needs, try this out, see if you are any closer to you answer :)
I copied MinDate in new measure window and got error message.
- Wild guess, date is a reserved word, try ‘date’ quoted
Tried 'Date', same error message.
this is what you need
taskBegDay3 = VAR _selected = SELECTEDVALUE('Date'[Date (bins)],0) return CALCULATE ( DISTINCTCOUNT(Task[TaskID]) ,filter(TaskStatus,TaskStatus[TaskStatus] in {"In Progress","Not Started"}) ,FILTER(ALL(Task), _selected > Task[createddate]) )