Unique Filter with threeway matching - New Column

[ChirSidh]

My data set has a Category (Project Number), Sub Category (Work order number), Close Status ( True/False).
One project has many work orders, what i would like to filter is If all the work orders of a close status is False then only it should filter the project name, even if one close status is also True then it should not filter. 
Please help me with column formula

[ChirSidh]

Hi vanessafvg yes it would be good if the result is limited to first row of project number rest of the rows for the particular project  should be empty 

Also, can you please look at my other post as well, where i mentioned that I have used this formula in my dataset which is very large, i dont know for some strange reason each row is getting repeated 176 times, am i doing any mistake here?

1 Result =

2 var Project = SELECTEDVALUE(PFT[ Project])

3 var Result = CALCULATE(max(PFT[ Project]),ALLEXCEPT('PFT',PFT[ Project]), PFT[Close?]= FALSE())

4 Return Result

[ChirSidh]

I have used this formula in my dataset which is very large, i dont know for some strange reason each row is getting repeated 176 times, am i doing any mistake here?

Result =

var project = SELECTEDVALUE(PFT[ Project])

var result = CALCULATE(max(PFT[ Project]), ALLEXCEPT(PFT,PFT[ Project]), PFT[Close?] = "FALSE")

Return result

[vanessafvg]

please see pbix attached and let me know if you have any questions.

this is the code i used

Result =

var project = SELECTEDVALUE('Table'[Project])

var result = CALCULATE(max('Table'[Project]), ALLEXCEPT('Table','Table'[Project]), 'Table'[Close?] = FALSE())

Return result

[ChirSidh]

Thank you Vanessafvg, this works, however the result gets repeated in the rows, is there anyway we can show one project number just at one time like we do in Excel with Unique(filter) formula

[vanessafvg]

I am not a excel developer, I work on big data models so I am not sure entirely sure what you mean.   are you saying you only want it to return for one row as it does in your screensshot ?  can you explain to me a litte bit more of what you are trying to do by only returning it once.  If it must be returned once which row?   

Power bi is a tabular data model, it doesnt work in the same way as excel.  When it assess the result it will do that for each row and it will return data for each row.   If you need it to return for only one row you will need to tell it which row to return it to

[ChirSidh]

 Project	Work Order No	Close?	Expected Result
82100251	82100251-012	FALSE	82100251
82100251	82100251-012	FALSE	
82100251	82100251-013	FALSE	
82100251	82100251-014	FALSE	
82100251	82100251-015	FALSE	
72103803	72103803-159	TRUE	
72103803	72103803-163	TRUE	
72103803	72103803-178	TRUE	
72103803	72103803-184	TRUE	
72103803	72103803-189	TRUE	
72103803	72103803-197	TRUE	
72103803	72103803-200	TRUE	
72103804	72103804-189	TRUE	72103804
72103804	72103804-197	FALSE	
72103804	72103804-200	TRUE	
90005981	90005981-100	TRUE	
90005981	90005981-400	TRUE	
90009205	90009205-105	FALSE	90009205
90009205	90009205-106	TRUE	
90009205	90009205-107	TRUE	
​

Project	Work Order No	Close?	Expected Result
82100251	82100251-012	FALSE	82100251
82100251	82100251-012	FALSE
82100251	82100251-013	FALSE
82100251	82100251-014	FALSE
82100251	82100251-015	FALSE
72103803	72103803-159	TRUE
72103803	72103803-163	TRUE
72103803	72103803-178	TRUE
72103803	72103803-184	TRUE
72103803	72103803-189	TRUE
72103803	72103803-197	TRUE
72103803	72103803-200	TRUE
72103804	72103804-189	TRUE	72103804
72103804	72103804-197	FALSE
72103804	72103804-200	TRUE
90005981	90005981-100	TRUE
90005981	90005981-400	TRUE
90009205	90009205-105	FALSE	90009205
90009205	90009205-106	TRUE
90009205	90009205-107	TRUE

[vanessafvg]

please provide the data in text format so its easy to copy and paste into a power bi file and do this for you.