How can I find common items for filtering in slicer

[Anonymous]

Hi,

I have survey data like this:

Answers:

UserID	SurveyName	ResponseID	QuestionDescription	AnswerText
user1	survey1	res1_1_1	question1	A
user1	survey1	res1_1_1	question2	A
user1	survey1	res1_1_1	question3	B
user1	survey1	res1_1_2	question2	B
user1	survey2	res1_2_1	question1	B
user1	survey2	res1_2_1	question2	C
user2	survey1	res2_1_1	question1	A
user2	survey1	res2_1_1	question2	C
user2	survey2	res2_2_1	quesrion1	B

And I have created 2 new table for my cases:

Table (7a) = DISTINCT('answers'[QuestionDescription])

Table (7b) = DISTINCT('answers'[SurveyName])

What I need is to find common users who responded to the two surveys below and find their distribution based on their answers to the question I chose. I can do this with the formula below.

Count of Cohort 7c = 
    VAR __tmpTable1 = 
        CALCULATETABLE(
            GENERATE(
                DISTINCT('answers'[UserID]),
                EXCEPT(
                    DISTINCT('answers'[SurveyName]),
                    CALCULATETABLE(DISTINCT('answers'[SurveyName]))
                )
            ),USERELATIONSHIP(answers[ResponseId],responses[ResponseID]),
            REMOVEFILTERS('answers'[AnswerText])
        )
    VAR __tmpTable2 = SUMMARIZE(__tmpTable1,[UserID])
    VAR __tmpTable3 = EXCEPT(DISTINCT('answers'[UserID]),__tmpTable2)
    VAR __CountMax = COUNTROWS(__tmpTable3)

    VAR __Survey = SELECTCOLUMNS('Table (7b)',"__SurveyName",[SurveyName])
    VAR __Question = SELECTCOLUMNS('Table (7a)',"__QuestionDescription",[QuestionDescription])
    VAR __tmpTable4 = DISTINCT(SELECTCOLUMNS(FILTER('answers',[SurveyName] IN __Survey && [QuestionDescription] IN __Question),"UserID",[UserID]))
    VAR __CountMin = COUNTROWS(__tmpTable4)
    VAR __tmpTable5 = FILTER(__tmpTable4,[UserID] IN __tmpTable3)
    VAR __Count = COUNTROWS(__tmpTable5)
RETURN
    SWITCH(TRUE(),
        ISBLANK(COUNTROWS(__tmpTable4)),BLANK(),
        __CountMin < __Count,__CountMin,
        __Count > __CountMax,__CountMax,
        __Count
    )

When I select question and 2th surveyName I can find true common users and I see their distribution for answers correctly. But When I select a part of pie chart I can not see true data:

How can I solve this?

[Anonymous]

Hi All, Can you help me, I can not still fix it and It is a big problem for me.

Best,

[Anonymous]

Hi There, Do you have any solution about my problem?

Best,

[v-deddai1-msft]

Hi @Anonymous ,

I tried to understand the measure you provided, but there is no [UserID] column in answers table in the sample data you give us. Would you please explain more about your measure?

Best Regards,

Dedmon Dai

[Anonymous]

Hi @v-deddai1-msft ,

Yes, I have send our original data and I'm sorry but we dont have UserID in answer table. I generally merge Answers table and Response Table with ResponseID. Then I get answer table like User ID | Response ID | Question | Answer Choice| Answer Text

And also we dont have question description in Answer Table so I merge Answer Table and Qestion Table with SQ. and we dont have QuestionChoices in Answer Table so I merge Answer Table and QuestionChoices Table wit SQC. 

My last answwer table seems that like this:

_id | ResponseID | UserID | QuestionID | DataExportTag | QuestionDescription | QuestionChoices | Answer Choices | Answer Text | SQ | SQC

Then I create measurements on this Answer Table. You can do it as this way. But If you know a more effectve way you can show us and we would be grateful to you

Best

[Anonymous]

Hi @v-deddai1-msft ,

You can see my power bi report in there:

https://yadi.sk/d/wrWkGRR7ZCmgCQ

I try to explain what I need from this report in report.

I hope you can help me.

[v-deddai1-msft]

Hi @Anonymous ,

It depends on the relationship in your data table. Would you please show us sample pbix for reference by onedrive for business if possible? Then we can help you more correctly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi @aj1973,

Actually, I fixed this problem with adding a new answer table and changing measurement as this:

Table (7c) = DISTINCT('answers'[AnswerText])

Count of Cohort 7c = 
    VAR __tmpTable1 = 
        CALCULATETABLE(
            GENERATE(
                DISTINCT('answers'[UserID]),
                EXCEPT(
                    DISTINCT('answers'[SurveyName]),
                    CALCULATETABLE(DISTINCT('answers'[SurveyName]))
                )
            ),
            REMOVEFILTERS('answers'[AnswerText])
        )
    VAR __tmpTable2 = SUMMARIZE(__tmpTable1,[UserID])
    VAR __tmpTable3 = EXCEPT(DISTINCT('answers'[UserID]),__tmpTable2)
    VAR __CountMax = COUNTROWS(__tmpTable3)

    VAR __Survey = SELECTCOLUMNS('Table (7b)',"__SurveyName",[SurveyName])
    VAR __Question = SELECTCOLUMNS('Table (7a)',"__QuestionDescription",[QuestionDescription])
    VAR __AnswerText = SELECTCOLUMNS('Table (7c)',"__AnswerText",[AnswerText])
    VAR __tmpTable4 = DISTINCT(SELECTCOLUMNS(FILTER('answers',[SurveyName] IN __Survey && [QuestionDescription] IN __Question && [AnswerText] IN AnswerText),"UserID",[UserID]))
    VAR __CountMin = COUNTROWS(__tmpTable4)
    VAR __tmpTable5 = FILTER(__tmpTable4,[UserID] IN __tmpTable3)
    VAR __Count = COUNTROWS(__tmpTable5)
RETURN
    SWITCH(TRUE(),
        ISBLANK(COUNTROWS(__tmpTable4)),BLANK(),
        __CountMin < __Count,__CountMin,
        __Count > __CountMax,__CountMax,
        __Count
    )

But I see an other problem in this time, when I a new question slicer and a answer pie chart and If I click a part of first pie part, second pie chart doesnt change with it.

[aj1973]

Hi @Anonymous 

Check out the relationship of your tables in the model, change the relationship from single to both.

[aj1973]

Hi @Anonymous 

check out the filters of the Pie Chart visual

maybe something at odd.

[m3tr01d]

Hello!

Try something like this  with two measures:

NbAnswers = COUNTROWS( 'SurveyResponse' )

MostCommon_Count = 
--Find the answer with the most answers
VAR _TopN = TOPN( 1, VALUES( SurveyResponse[AnswerTex] ), [NbAnswers], DESC )

--Return the number of answers for the Top 1
RETURN
CALCULATE(
    [NbAnswers],
    _TopN
)