Forum Discussion
Conditional Row Filtering
Hello All -
Looking for a bit of help here. My table of data contains duplicate entries of some orders due to the order status. Each order gets an entry appended to it based on order status update, so in essence you can view a history of the order from processing through to completed. Here's what that looks like:
You'll notice that order #3439 appears in two rows, one with order status as "processing" and then one as status "completed". I only need the row that contains "completed", but there are some orders where status is "processing" that I still need to, so I can't just filter for "completed". The logic has to be, if order Number has status of completed, use that row, else use the processing row.
Thanks for any suggestions.
Hi , a68tbird
For you requirement, you can try follow steps:
1.create a custom column 'count'
=let Number=[Number] in Table.RowCount(Table.SelectRows(#"Changed Type",each Number = [Number]))2. select custom column 'count' and apply filter conditions to this column
3.After removing unnecessary columns,you will get the result you want.You can check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
a68tbird add a measure for filter and then use this measure to use visual level filter where measure value is 1
Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
Hi a68tbird,
I think you are trying to filter the rows in power query, if that is the case you will need to create an index per order group in descending order and then filter out all indexes not equal to 1. please see this post from Reza Rad, https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query.
One thing you will want to ensure is that you sort the data by order and then status to ensure that index number 1 gets assigned to the completed row.
Attached please find a simple pbix with a working power query pipeline based on your data
Hi , a68tbird
For you requirement, you can try follow steps:
1.create a custom column 'count'
=let Number=[Number] in Table.RowCount(Table.SelectRows(#"Changed Type",each Number = [Number]))2. select custom column 'count' and apply filter conditions to this column
3.After removing unnecessary columns,you will get the result you want.You can check my sample file for more details.
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Thanks all for the awesome suggestions!
