Count Unpivot Table (Distinct does not give right results)

ketanvaria — Sat, 16 Nov 2024 21:45:18 GMT

I have an original table with 3 records.

Original Table	Question A	Question B
1	Expert	Expert
2	Basic	Basic
3	Expert	No Knowledge

So A simple KPI count function gives me the answer 3 (which is correct!)

I have had to unpivot the table to do some more analysis

UNPIVOT TABLE
Record	Question	Skill
1	A	Expert
2	A	Basic
3	A	Expert
4	B	Expert
5	B	Basic
6	B	No Knowledge

So A simple KPI count function gives me the answer 6 (which is incorrect!)

I tried DISTINCT COUNT function on Question but got the result 2 (which is incorrect!)

What function do I use to get the answer 3? I don't want to have 2 table so want to use the Unpivot table throughout.

Re: Count Unpivot Table (Distinct does not give right results)

jan_vandersypt — Sun, 09 Dec 2018 10:29:33 GMT

You'll have to include the recno from the original table in your unpivot table. I guess you have some kind of a participant_id, the person who filled in the two questions? You should use a distinct count on that field.

topic Count Unpivot Table (Distinct does not give right results) in App Development

Count Unpivot Table (Distinct does not give right results)

Re: Count Unpivot Table (Distinct does not give right results)