Forum Discussion

JulietZhu's avatar
JulietZhu
Icon for Helper IV rankHelper IV
7 years ago
Solved

Calculated column not group by dimension column

 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

Need Help
  • themistoklis's avatar
    themistoklis
    7 years ago

    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

4 Replies

    • themistoklis's avatar
      themistoklis
      Icon for Community Champion rankCommunity Champion
      7 years ago

      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's avatar
        JulietZhu
        Icon for Helper IV rankHelper IV
        7 years ago

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