AND Filtering in the report

[Anonymous]

Hi There,

I have some table like this:

Responses Table:

Answer Table:

And the visulations:

My first question is how can I find usercount which has responseID both selected surveyName. So the count of userID must show only 2 (ABC1 and ABC2)

And the second question is after I find this 2 user, I want to see their answers distribution for any questiontext in any survey. So I want to click 2 or more surveyname in the slicer, the card must show count of userID entering all selected surveys. And then I select another slicer a survey and question text it show me this users ditribution for this selected questiontext in selected surveyname.

Thank you for your helping.

Best,

[Anonymous]

Hi @Greg_Deckler 

If I add  a new pie chart and I want to see selected users distibution in old pie chart in new pie chart. Is that possible?
So I want to see this output:

[amitchandak]

@Anonymous ,

A measure like this

measure =
var _sel = maxx(allselected(Response), Response[Survey Name])
return
countx(filter(summarize(Response, Response[user_id], "_1", distinctcount(Response[Survey_id])),[_1] =2),[user_id])

All selected surveys are there with user id

[Anonymous]

Hi @amitchandak and @Greg_Deckler 

Thank you for your responses but my main problem is:

Can you help me?

[Greg_Deckler]

@Anonymous OK, if you used Cohort for your and slicer, you should have a line like this:

VAR tmpTable3 = EXCEPT(VALUES(Diagnosis[Patient]),tmpTable2)

You could have your measure perform the logic of instead of returning countrows to only return whatever calculation you want for values (patients) IN tmpTable3 

[Anonymous]

@Greg_Deckler I have used this code in Cohort:

But In my report:

When I select other SurveyName and Questiontext in there, It shows all entered this survey. But I want to show only this 2 users count in the pie chart.

So, In my report:

[Greg_Deckler]

@Anonymous Right, so use the same Cohort measure syntax in a new measure but change the RETURN statement to something like:

RETURN
  IF(MAX([UserID]) IN __TmpTable3,[Old Measure for Value you want to display in pie chart],BLANK())

[Anonymous]

@Greg_Deckler What is that mean "Old Measure for Value you want to display in pie chart" I didnt understand?

I did it like this but again It show wrong data for me. q3a2text must be 2 in the pie chart.

[Greg_Deckler]

@Anonymous What was the value you were displaying in your pie chart originally?

[Anonymous]

@Greg_Deckler you can see my previous message, q3a2text is always shown 3 even if I run your sent this code:

RETURN
  IF(MAX([UserID]) IN TmpTable3,[Count of Cohort],BLANK())

But I need q3a2text must be shown 2 because 2 userID answer the question as q3a2text and at the same time they join this two survey.  

[Anonymous]

Hi @Greg_Deckler  do you have any solution for this?

Best,

[Greg_Deckler]

@Anonymous Sample data as text that I can copy and paste so I can mock this up or even better, the PBIX file.

[Anonymous]

Hi @Greg_Deckler 

This are the sample data:

Answers:

ResponseID	QuestionID	QuestionText	AnswerID	AnswerText
XYZ1	Q1	qtext1	q1a1	q1a1text
XYZ1	Q2	qtext2	q2a1	q2a1text
XYZ1	Q3	qtext3	q3a1	q3a2text
XYZ2	Q1	qtext1	q1a2	q1a2text
XYZ2	Q3	qtext3	q3a1	q3a1text
XYZ3	Q1	qtext1	q1a1	q1a1text
XYZ3	Q2	qtext2	q2a2	q2a2text
XYZ3	Q3	qtext3	q3a2	q3a2text
XYZ4	Q1	qtext1	q1a1	q1a1text
XYZ4	Q2	qtext2	q2a1	q2a1text
XYZ4	Q3	qtext3	q3a2	q3a2text
XYZ5	Q1	qtext1	q1a3	q1a3text
XYZ5	Q2	qtext2	q2a1	q2a1text
XYZ6	Q2	qtext2	q2a3	q2a3text
XYZ6	Q3	qtext3	q3a1	q3a1text
XYZ7	Q1	qtext1	q1a2	q1a2text
XYZ7	Q3	qtext3	q3a2	q3a2text
XYZ8	Q1	qtext1	q1a1	q1a1text
XYZ8	Q2	qtext2	q2a2	q2a2text
XYZ8	Q3	qtext3	q3a1	q3a1text

Responses:

SurveyID	SurveyName	UserID	ResponseID
SV_1234	survey1	ABC1	XYZ1
SV_1234	survey1	ABC1	XYZ2
SV_1234	survey1	ABC2	XYZ3
SV_1234	survey1	ABC3	XYZ4
SV_5678	survey2	ABC1	XYZ5
SV_5678	survey2	ABC4	XYZ6
SV_5678	survey2	ABC3	XYZ7
SV_9821	survey3	ABC1	XYZ8

[Anonymous]

Hi @Greg_Deckler 

Do you have any solution about this?

[Anonymous]

Hi There,

It is so important for us. It couldn't be fixed. Can you help us?

[Greg_Deckler]

@Anonymous I mocked this up in the attached PBIX below sig. Page 6. I get 3 because with the slicer settings as shown, ABC1 is in there and ABC2 and ABC3 are also in there. So 3. Not sure what you are looking for but the data clearly says 3.

[Anonymous]

Hi @Greg_Deckler ,

As you can see in Page 6, the number of users who entered both surveys is 2 ABC1 and ABC3. You can check it your Table (6a)

I want to see this two users' answers' distribution in selected other surveys.

In this case I want to see this two users' answers' distribution in survey1 and qtext3.

So If I count distinct users for any answers, it has to be max 2 because I have 2 users. I try to explain in a ss:

[Greg_Deckler]

@Anonymous OK, still something going on that I am working through but I do have a minor work-a-round operating that seems to do the trick. Same page. PBIX below.

[Anonymous]

@Greg_Deckler , If I select both survey in second survey filter, yes we see right pie chart. But I want to select both or more survey in first survey filter slicer after I see two users entered this this both surveys then I select one survey in second survey slicer and then I must see this two users distribution for selecting questions' answers. So pie chart must include only two users max for all answers slice of pie.

[Greg_Deckler]

@Anonymous I have no idea what you are talking about. What?

[Anonymous]

@Greg_Deckler ,

I- I choose two survey and what I want to see is the number of common users participating in both survey

II-  I choose any survey from the SurveyName filter below because I want to see the distribution of the two users selected above in the selected survey below.

III- I chose a question I wanted in QuestionText because I want to see the distributiın of the answers given by the two users above to the question qtext3 in survey1 on the basis of users.

IV- Since I want to see the distribution of the answers given by the common users of the two surveys selected in the first filter to the selected survey in the second filter and the selected question in the third filter, a pie slice here can be at most 2. Because I only have 2 users that meet this criterion: ABC1 and ABC3.

I dont know how can I explain more.