Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tinho90
New Member

Cross table using same column

Hey guys

 

I have a dataset of the following structure:

 

 

CaseQuestionPossible answerTRUE/FALSE
1AZ1TRUE
1AZ2TRUE
1BX1FALSE
1BX2FALSE
1BX3TRUE
1CY1TRUE
1CY2FALSE
1CY3TRUE
1CY4FALSE
2AZ1FALSE
2AZ2TRUE
2BX1TRUE
2BX2FALSE
2BX3TRUE
2CY1FALSE
2CY2TRUE
2CY3FALSE
2CY4TRUE

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:

  BBB
QuestionPossible answerX1X2X3
AZ1  1
AZ21 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

4 REPLIES 4
MFelix
Super User
Super User

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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 "NYCare 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hey @MFelix

 

Exactly

 

// Martin

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.