Calculated column not group by dimension column

[JulietZhu]

 I have task fact table and tasktype dimention table. 2 calcuated columns. Total number is correct. But group by TaskType, TashBegDay2 measure is not working and it always pickup the total number. Any thought about this. Thanks

 DAX for 2 measrues are below

TaskCreated = CALCULATE(DISTINCTCOUNT(Task[TaskID]))

TaskBegDay =
VAR _selected = SELECTEDVALUE('Date'[Date],min('Date'[Date]))
return
CALCULATE ( DISTINCTCOUNT(Task[TaskID]),filter(TaskStatus,TaskStatus[TaskStatus] in {"In Progress","Not Started"})
,FILTER(ALL(Task), _selected > Task[createddate])) 
+ CALCULATE ( DISTINCTCOUNT(Task[TaskID]),filter(TaskStatus,TaskStatus[TaskStatus] ="Completed")
,FILTER(ALL(Task), _selected > Task[createddate] && _selected <= Task[CompletedDate] ))

 

Dummy data link

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

[JulietZhu]

Please help. Thanks

[themistoklis · Accepted solution]

@JulietZhu

Try this formula:

TaskBegDay2 = 
VAR _selected = SELECTEDVALUE('Date'[Date],0)
return
    CALCULATE (  
                 DISTINCTCOUNT(Task[TaskID])
                        ,filter(TaskStatus,TaskStatus[TaskStatus] in {"In Progress","Not Started"})
                        ,FILTER(ALLEXCEPT(Task, TaskType[TaskTypeCategory]), _selected > Task[createddate])
                          
              )
    +  CALCULATE (          
                 DISTINCTCOUNT(Task[TaskID])
                        ,filter(TaskStatus,TaskStatus[TaskStatus] ="Completed")
                        ,FILTER(ALLEXCEPT(Task, TaskType[TaskTypeCategory]), _selected > Task[createddate] &&  _selected <= Task[CompletedDate] )
                          )

Instead of ALL use ALLEEXCEPT

[JulietZhu]

@themistoklis. It works. This bothered me the whole day yesteday. You are awesome.  What is difference all and allexcept. Thanks 

[themistoklis]

@JulietZhu

ALLEXCEPT simply excludes the specified field from the calculation of the metric 🙂