Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.