Forum Discussion
Helper INeed an additional column based on 2 other columns?
Hi Everyone!
I have a large data set, and each project has multiple lines for different status, here is a simplified version below:
If a project does not have "closed" state, I want to mark each line for the project as "in progress". In the example below thats project 2 & 4, since 1 and 3 are closed.
Is there any way to do this in a column or measure?
Thanks in advance!! 🙂
| Project # | Status | GOAL |
| 1 | open | |
| 1 | review | |
| 1 | pending | |
| 1 | closed | |
| 2 | open | in progress |
| 2 | review | in progress |
| 2 | pending | in progress |
| 3 | closed | |
| 4 | open | in progress |
| 4 | pending | in progress |
PBINewbie920 try this measure, pbix is attached
Measure = IF ( CALCULATE ( MAX ( 'Table 1'[Status] ), 'Table 1'[Status] IN { "closed" }, ALLEXCEPT ( 'Table 1', 'Table 1'[Project #] ) ) <> "closed", "in progress" )
4 Replies
- amitchandak
Super User
PBINewbie920 , Try a new column
New column =
var _1 = countx(filter(Table, [Project #] =earlier([Project #]) && [Status] = "closed"), [Project #])
return
if(isblank(_1), "in progress", blank()) 
smpa01Community Champion
PBINewbie920 try this measure, pbix is attached
Measure = IF ( CALCULATE ( MAX ( 'Table 1'[Status] ), 'Table 1'[Status] IN { "closed" }, ALLEXCEPT ( 'Table 1', 'Table 1'[Project #] ) ) <> "closed", "in progress" )- v-easonf-msft
Community Support
Hi, PBINewbie920
It seems that your problem has been solved.
If so, please accept the helpful answers as solution to close this thread.
Other community members will easily find the solution when they get the same issue
Best Regards,
Community Support Team _ Eason - Ashish_Mathur
Hi,
This calculated column formula works
=if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[Project #]=EARLIER(Data[Project #])&&Data[Status]="closed"))>0,BLANK(),"Pending")Hope this helps.
