topic Re: Progress Measure in DAX Commands and Tips

Progress Measure

carolinastylin — Tue, 16 Jul 2024 16:32:41 GMT

I built a progress measure that is working but I'm trying to optimize. I have 5 different project types with varying numbers of tasks. I wrote a measure to divide the total completed tasks by the number of incomplete tasks to get the percentage completed. The project types are specified in one column. Need help determining the best method to optimize this measure because the “Valuelist” measure is affecting other measures that I’ve created. FYI... The Completed/Incomplete tasks measures are determined by if a date field is blank or not. All data is being pulled from a SharePoint list. There are 15 columns but not all apply to each ProjectType (I much rather filter the columns that apply for the DAX formula for the specific ProjectType).

Thanks in advance!

Progress =

IF.EAGER('Table'[ValueList] = {"ProjectType1"},

DIVIDE([COMPLETED_TASKS1],[COMPLETED_TASKS1]+[INCOMPLETE_TASKS1],0)+0,

IF.EAGER('A6FP_AWAKEN Program Database'[ValueList] = {"ProjectType2"},

DIVIDE([COMPLETED_TASKS2],[COMPLETED_TASKS2]+[INCOMPLETE_TASKS2],0)+0 -- and so on

Re: Progress Measure

Anonymous — Wed, 17 Jul 2024 01:30:35 GMT

Hi carolinastylin ,

You can try formula like below to create measure:

CompletedTasks_ProjectType1 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType1", NOT(ISBLANK('Table'[CompletionDate])) ) CompletedTasks_ProjectType2 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType2", NOT(ISBLANK('Table'[CompletionDate])) ) CompletedTasks_ProjectType3 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType3", NOT(ISBLANK('Table'[CompletionDate])) )

IncompleteTasks_ProjectType1 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType1", ISBLANK('Table'[CompletionDate]) ) IncompleteTasks_ProjectType2 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType2", ISBLANK('Table'[CompletionDate]) ) IncompleteTasks_ProjectType3 = CALCULATE(COUNTROWS('Table'), 'Table'[ProjectType] = "ProjectType3", ISBLANK('Table'[CompletionDate]) )

Progress = SWITCH( TRUE(), MAX('Table'[ProjectType]) = "ProjectType1", DIVIDE([CompletedTasks_ProjectType1], [CompletedTasks_ProjectType1] + [IncompleteTasks_ProjectType1], 0), MAX('Table'[ProjectType]) = "ProjectType2", DIVIDE([CompletedTasks_ProjectType2], [CompletedTasks_ProjectType2] + [IncompleteTasks_ProjectType2], 0), MAX('Table'[ProjectType]) = "ProjectType3", DIVIDE([CompletedTasks_ProjectType3], [CompletedTasks_ProjectType3] + [IncompleteTasks_ProjectType3], 0), BLANK() )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Progress Measure

carolinastylin — Wed, 17 Jul 2024 12:57:13 GMT

Thanks! I will give this a try...