Distinct count two different columns by group

[Anonymous]

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.

[robadob · Accepted solution]

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 🙂

[Anonymous]

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.

[robadob]

I might have misunderstood but try this:

1. edit the query for your table,
2. select id column
3. unpivot other collumns

so it looks like: 

Then save the query and make your matrix:

Result:

Hope the helps! 🙂 

[Anonymous]

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.