Forum Discussion
Helper IIUnique Filter with threeway matching - New Column
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
7 Replies
ChirSidhProject 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
Community Champion
please provide the data in text format so its easy to copy and paste into a power bi file and do this for you.
- 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
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 - 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
