Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Hi @Tinho90,
Just to understand better, you want to relate all the answers from all the questions to all other questions?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |