DAX help

[JulietZhu]

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(*)

from task t

join [dbo].[TaskStatus] ts on ts.[TaskStatusID]=t.[TaskStatusID]

where [TaskStatus] in ('In Progress','Not Started') and convert(varchar,[CreatedDate],112) < '20180801'

DAX I am using is below

TaskBegDay = CALCULATE (DISTINCTCOUNT(Task[TaskID]),filter(TaskStatus,TaskStatus[TaskStatus] in {"In Progress","Not Started"}), filter(ALL ( 'Task'[CreatedDate]),Task[CreatedDate]<min('Date'[Date] )))

But return blank. Please help Thanks.

Three tables include Date, Task,TaskStatus with relationship and dummy data is below

https://1drv.ms/u/s!AlYpYKwSuOKxhFRF5v9mKLSJHvMs

[Nickgastaldi]

try removing the all from TASK table filter, that would conflict with relation filter right above.

maybe something like this

yourmeasurename :

VAR @minDate = min('Date'[Date]) 

return

TaskBegDay = 
    CALCULATE (          
                 DISTINCTCOUNT(Task[TaskID])
                           ,filter(TaskStatus,
                                   TaskStatus[TaskStatus] in {"In Progress","Not Started"}
                                    )
                          ,Task[CreatedDate] > @minDate )
              )
         )

 

also i dont know if this is intented but the date filter you are applying to dates, you are trying to filter task outside the Date table i inverted it just in case but fit your need 🙂
hope this helps 

[JulietZhu]

@Nickgastaldi,  I tryied your DAX and remove all, but it still returns blank.

I need count all the tasks created before 1st of each month, which is minDate of each month.  My report is monthly and slice has to be month. For example, if Aug 2018 is choose, I need calculatued all the task created before Aug 1st 2018.  Please help. Thanks

[Nickgastaldi]

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 🙂  

[JulietZhu]

I copied MinDate in new measure window and got error message. 

[Nickgastaldi]

Wild guess, date is a reserved word, try ‘date’ quoted

[JulietZhu]

Tried 'Date', same error message.

[Nickgastaldi · Accepted solution]

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])
                          
              )

[JulietZhu]

@Nickgastaldi, it works. Thanks so much. Try to understand selectedvalue you are using. 

[Nickgastaldi]

SELECTEDVALUE(column)
return the value of the selected row if it was used in a slicer or selected by user

if you have a table for instance, and wants to do a calculation based on a row selected by the user, SELECTEDVALUE(YourColumn) will return the value on that row.

its very straight forward 🙂

[JulietZhu]

@Nickgastaldi, Thanks for explanation

[JulietZhu]

@Nickgastaldi, I will test your new DAX. 

Also I have dummy data in my orignal post. I can past here too. 

https://1drv.ms/u/s!AlYpYKwSuOKxhFRF5v9mKLSJHvMs

[JulietZhu]

Anybody help? Wating for answer to continute my project. Thanks