- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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! 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi! Thanks for the help.
Is there a way other than by unpivoting columns? Thing is, this is a great simplification of my real data (one that already has been unpivoted). I want to avoid doing it again.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
08-28-2024 02:53 AM | |||
08-17-2023 01:27 AM | |||
05-04-2024 08:02 PM | |||
07-13-2023 03:50 PM | |||
09-25-2024 07:14 AM |
User | Count |
---|---|
141 | |
117 | |
80 | |
65 | |
47 |