Forum Discussion
Create custom column to test if 'status' column has the value 'complete' for all records w/ same ID
I have two tables with a one-to-many relationship as follows:
My goal is to create a column in the second table (the table with many/duplicate wr_id values) that will indicate whether the 'status' has a value of 'Complete' for all records with that wr_id. For example, wr_id 141 has 3 records in the many table, where the status is 'Complete' for all 3 records. This means I would want a value of 'yes' in my custom column for all records with a wr_id of 141. If there is a wr_id in the many table that doesn't have 'Complete' for all the records associated to the wr_id than i would want a value of 'no' in my custom column.
any help? thank you!
So you could this with a column expression like the following
All Complete =var _wr_id = [wr_id]var wr_id_rows = COUNTROWS(filter('Table','Table'[wr_id] = _wr_id))var complete_rows = COUNTROWS(filter('Table','Table'[wr_id] = _wr_id && [status] = "Complete"))return if (wr_id_rows = complete_rows, "yes","no")But I would have thought that a column like this would have made more sense on the one side of the relationship...
2 Replies
- d_gosbell
Super User
So you could this with a column expression like the following
All Complete =var _wr_id = [wr_id]var wr_id_rows = COUNTROWS(filter('Table','Table'[wr_id] = _wr_id))var complete_rows = COUNTROWS(filter('Table','Table'[wr_id] = _wr_id && [status] = "Complete"))return if (wr_id_rows = complete_rows, "yes","no")But I would have thought that a column like this would have made more sense on the one side of the relationship...Thank you so much! Works perfectly.
And you are right, i altered the code above to work in the one-table.
thanks again,
Ben
