s d Posted September 12, 2019 Share Posted September 12, 2019 delete Link to comment Share on other sites More sharing options...
Kirsten Smith (she/her) Posted September 12, 2019 Share Posted September 12, 2019 You could create a new column with an expression like: count([Employee #]) OVER ([Employee #]) as [#Items] then use Filtering or Limit data on this column to only show rows where it is equal to 2 Link to comment Share on other sites More sharing options...
Kirsten Smith (she/her) Posted September 12, 2019 Share Posted September 12, 2019 This expression doesn't even look at the Items column, it just counts the number of times each employee number appears in the table. The output looks like: Employee number,Item,#Items 1001,Mobile,2 1001,Desk Phone,2 1002,Mobile,1 1003,Desk Phone,1 Link to comment Share on other sites More sharing options...
Kirsten Smith (she/her) Posted September 12, 2019 Share Posted September 12, 2019 Use a case() statement to get just the rows you want: case [item] when "Desk Phone" then [Employee number] when "Mobile" then [Employee number] else 0 end as [Valid] then use the count() on this column: count([Valid]) OVER ([Valid]) as [#Items] Link to comment Share on other sites More sharing options...
Kirsten Smith (she/her) Posted September 12, 2019 Share Posted September 12, 2019 Double-check your formatting in the expression, because it works for me. Link to comment Share on other sites More sharing options...
Kirsten Smith (she/her) Posted September 12, 2019 Share Posted September 12, 2019 Okay, then take off the "as [#Items]" and add it as Column Name, or change the Column name after it's calculated Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now