The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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
Please help. Thanks
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
@themistoklis. It works. This bothered me the whole day yesteday. You are awesome. What is difference all and allexcept. Thanks
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
101 | |
81 | |
62 | |
54 |
User | Count |
---|---|
249 | |
119 | |
115 | |
94 | |
70 |