Forum Discussion

ChirSidh's avatar
ChirSidh
Icon for Helper II rankHelper II
3 years ago

Unique 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 ...
Need Help
vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
3 years ago

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's avatar
ChirSidh
Icon for Helper II rankHelper II
3 years ago

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's avatar
    vanessafvg
    Icon for Community Champion rankCommunity Champion
    3 years ago

    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