Project Status from Multiple Status

[kpeterson]

I'm looking to create a new column or even a new table that looks at criteria in two columns.  The new column needs to look at the "Project Regulatory Code" and look at whether all projects for each regulatory code group are Complete and return Complete or In Progress if they are not.  I can't seem to figure out how to accomplish this. TIA!

[ryan_mayu · Accepted solution]

@kpeterson 

you can try this

Column = if(countx(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])),'Table'[Regular Phase])=COUNTX(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])&&'Table'[Status]="Complete"),'Table'[Regular Phase]),"Complete","In progress")

pls see the attachment below

[Ritaf1983]

Hi @kpeterson 

There are my steps in PQ to achieve a goal 

1. I duplicated the table

2. Removed from the new table all columns except for code and status :

3 Grouped the code by statuses and counted distinct rows

4. Merged this new table with the original:

6. created a conditional column that checks how many distinct statuses have code and if it is "complete"

7. removed unnecessary column of counting and switched off the loading to the model the table that i created for grouping

Result

pbix is attached

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

[ryan_mayu · Accepted solution]

@kpeterson 

you can try this

Column = if(countx(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])),'Table'[Regular Phase])=COUNTX(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])&&'Table'[Status]="Complete"),'Table'[Regular Phase]),"Complete","In progress")

pls see the attachment below

[kpeterson]

Thank you for your reply.  After some QC, I also need to add a line for "if all Not Started" then Not Started.  Is that possible to add? TIA!

[ryan_mayu]

@kpeterson 

pls try this

Column = if(countx(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])),'Table'[Regular Phase])=COUNTX(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])&&'Table'[Status]="Not Started"),'Table'[Regular Phase]),"Not Started",if(countx(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])),'Table'[Regular Phase])=COUNTX(FILTER('Table','Table'[Regular Phase]=EARLIER('Table'[Regular Phase])&&'Table'[Status]="Complete"),'Table'[Regular Phase]),"Complete","In progress"))

[kpeterson · Accepted solution]

Thank you for your reply and this worked really well. Thanks!

[ryan_mayu]

you are welcome