Forum Discussion
Distinct count two different columns by group
Hello,
I'm trying to count how many times a certain string value is repeated across a group of IDs. By using DISTINCTCOUNT this works perfectly when only counting a column, but I can't get around to count two different columns two create a visual.
Here's a sample of my data.
| ID | W1 | W2 |
| 1111 | Yes | Yes |
| 1111 | Yes | Yes |
| 1112 | No | Yes |
| 1112 | No | Yes |
| 1113 | Yes | Yes |
| 1113 | Yes | Yes |
The end result should be a Matrix like this:
| Y/N | W1 | W2 |
| Yes | 2 | 3 |
| No | 1 | 0 |
However, since I'm using the "W1" field in Rows of the matrix, I keep getting this result:
| Y/N | W1 | W2 |
| Yes | 2 | 2 |
| No | 1 | 1 |
Any help would be appreciated!
Thanks in advance.
Apologies, this is above my paygrade...
Maybe you could duplicate the table in query edit, strip out everything that isn't needed for this, unpivot it, link it back on ID:
If that doesn't I don't think I can solve it 🙂
4 Replies
I might have misunderstood but try this:
- edit the query for your table,
- select id column
- unpivot other collumns
so it looks like:
Then save the query and make your matrix:
Result:
Hope the helps! 🙂
Apologies, this is above my paygrade...
Maybe you could duplicate the table in query edit, strip out everything that isn't needed for this, unpivot it, link it back on ID:
If that doesn't I don't think I can solve it 🙂
Yes, this works! Thank you.
Another way I got around to do it was duplicating the tables (One containing ID and W1, the other ID and W2) and creating a relationship with a Table with a Field that contains "Yes" and "No", this field goes in the rows of the desired visual. That way I didn't need to unpivot.