Cross table using same column

[Tinho90]

Hey guys

I have a dataset of the following structure:

Case	Question	Possible answer	TRUE/FALSE
1	A	Z1	TRUE
1	A	Z2	TRUE
1	B	X1	FALSE
1	B	X2	FALSE
1	B	X3	TRUE
1	C	Y1	TRUE
1	C	Y2	FALSE
1	C	Y3	TRUE
1	C	Y4	FALSE
2	A	Z1	FALSE
2	A	Z2	TRUE
2	B	X1	TRUE
2	B	X2	FALSE
2	B	X3	TRUE
2	C	Y1	FALSE
2	C	Y2	TRUE
2	C	Y3	FALSE
2	C	Y4	TRUE

And so on with around 400 unique IDs and 20 questions and between 5-20 unique possible answers (and additional columns to identify other stuff). So it essentially survey data.

I would like to do a pivottable that have this structure:

		B	B	B
Question	Possible answer	X1	X2	X3
A	Z1			1
A	Z2	1		2

where the values in the cell are the distinctcount of ID, where the values are true for both dimensions in the table.

I have tried different possibilites, but are essentially strugling with the datastructure, since I don't know how to make this kind of distinctcount, since it is based within the same column. When I'm trying to make a measures, that is distinctcount on "ID" and make the pivot table it returns empty.

Any ideas? Help would be much appreciated!

// Martin

[MFelix]

Hi @Tinho90,

Not really sure how your date will transpose into the one you show as a result.

You show Question A on rows and Question B on columns but I don't understand how are you making this calculation since Questio A and B on your data model show as independent questions so the layout of your information will not give the expected result.

Can you please elaborate on how the data correlates to each other and how are you making the distinct count?

regards,

MFelix

[Tinho90]

Hi @MFelix

Thanks for your reply. I will try to elaborate. 

The ID are the key in the data. The datastructure are such that an ID represents a person. The question (A) could be something like "What food do you enjoy?" and the possible answers could be "Pizza" and/or "Burger, while the question B could be "Which areas do prefer to eat in?" and possible answers could be "NYC", "Washington", "LA" etc.

So the table I'm trying to create shows the number of unique IDs, for which both answer "Pizza" and "NYC" are true. 

In the datastructure there are around 30 different questions, which each have between 2 and 25 possible answers. Therefore I don't think it is viable to tranpose the data, since it would give hundres of column. And a measure that could capture this would be a very elegant solution.

Does this clarify the issue?

// Martin

[MFelix]

Hi @Tinho90,

Just to understand better, you want to relate all the answers from all the questions to all other questions?

Regards,

MFelix

[Tinho90]

Hey @MFelix

Exactly

// Martin