AND Filtering in the report

Anonymous Because you are screwing up the context with the AND slicer by putting the Survey in the matrix. By doing so, you have changed the context of what gets returned as a cohort (AND) because each line only has 1 survey. Thus the AND slicer part returns 3 because there are 3 people that have responded to Survey 1 and also responded to Survey 1. See how the context gets screwed up by doing that?

Take SurveyName out of the matrix visual and you will get the same answers as the pie chart.

Seems like the original problem is solved. I haven't received a single Kudo for any of this even though I have spent hours upon hours on this problem. You need to go learn and understand DAX context. 

Hi Greg_Deckler Thanks for your time and working. It is a good job. But I think power bi must be developed for AND function better. Because If we do that a part of function, other part shows us a new problem. I want to see users distribusiton in any where who participate one more surveys. But I see a lot of problems. You have fixed one, thank you. Best

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 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 OK, and the latest file I posted does exactly that. PBIX is posted again below. I have the top slicer ONLY connected to the card visualization that shows 2. The rest of the visualizations, including the pie chart are connected to the bottom set of slicers.

For that 2nd part (II), I don't see the impact or perhaps missing something but you would would need a disconnected table for that slicer because you can't have both of those slicers tied to the same column in the same table because it will screw up the cohort calculation.

Anonymous - OK, I determined that most of the problems were the result of a unnecessarily complex data model. So, I merged the tables and things became much easier. See attached PBIX, Page 7, Table (7), Table (7a) and Table (7b). Let me know but the end measures were these:

Count of Cohort 7 = 
    VAR __tmpTable1 = 
        GENERATE(
            DISTINCT('Table (7)'[UserID]),
            EXCEPT(
                DISTINCT('Table (7)'[SurveyName]),
                CALCULATETABLE(DISTINCT('Table (7)'[SurveyName]))
            )
        )
    VAR __tmpTable2 = SUMMARIZE(__tmpTable1,[UserID])
    VAR __tmpTable3 = EXCEPT(DISTINCT('Table (7)'[UserID]),__tmpTable2)
RETURN
    COUNTROWS(__tmpTable3)



Count of Cohort 7a = 
    VAR __Survey = MAX('Table (7b)'[SurveyName])
    VAR __Question = MAX('Table (7a)'[QuestionText])
    VAR __CountMax = CALCULATE([Count of Cohort 7],REMOVEFILTERS('Table (7)'[AnswerText]))
    VAR __Table = SUMMARIZE(FILTER('Table (7)',[SurveyName]=__Survey && [QuestionText]=__Question),[UserID])
    VAR __Count = COUNTROWS(__Table)
RETURN
    IF(__Count>__CountMax,__CountMax,__Count)

Anonymous Try this one. Attached below.

Anonymous You'll have to remove the relationships between 7 and 7b and then implement a complex selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

I have mocked this up in the updated PBIX file. You create this complex selector:

Selector = 
    VAR __CurrentSurvey = MAX('Table (7b)'[SurveyName])
    VAR __Table = SELECTCOLUMNS('Table (7)',"__Survey",[SurveyName])
RETURN
    IF(__CurrentSurvey IN __Table,1,0)

And you use this in the Filters pane for your second slicer. Remember to remove the relationship between 7 and 7b, it is already done in the attached PBIX file. This Selector measure filters your second slicer as if the tables were related.

Anonymous Updated PBIX attached, basically change the measure to this:

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

    VAR __Survey = SELECTCOLUMNS('Table (7b)',"__SurveyName",[SurveyName])
    VAR __Question = SELECTCOLUMNS('Table (7a)',"__QuestionText",[QuestionText])
    VAR __tmpTable4 = DISTINCT(SELECTCOLUMNS(FILTER('Table (7)',[SurveyName] IN __Survey && [QuestionText] IN __Question),"UserID",[UserID]))
    VAR __tmpTable5 = FILTER(__tmpTable4,[UserID] IN __tmpTable3)
    VAR __Count = COUNTROWS(__tmpTable5)
RETURN
    IF(ISBLANK(COUNTROWS(__tmpTable4)),BLANK(),IF(__Count > __CountMax,__CountMax,__Count))

Anonymous No arguments over the AND slicer, it's a tricky problem and causes all sorts of headaches!

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

Hi Greg_Deckler 

This are the sample data:

Answers:

Responses:

Hi Greg_Deckler 

Do you have any solution about this?

Hi There,

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

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 , 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.

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

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.

Greg_Deckler 

I- Users participating in both surveys are ABC1 and ABC3. You can check it Table (6a)

II- I want to see the distribution of the answers these two users gave to the question qtext3 in survey2.

III- In this graph, you show that one user responded q3a1text and another user answered q3a2text.

IV- However, as you can see in the tables on the right, only one of the two people who participated in both survey1 and survey2 surveys answered the question of qtext3 in survey2. it is also q3a2text and ABC3.
The q3a1text response appears to have been answered by the ABC4 person in survey2 or by the person ABC1 in survey1.
So in this case, 1 and q3a2text should appear in the entire pie chart.

Greg_Deckler ,

The report you sent is working approximately correctly.

I- Our problem is When I select surveys in SurveyName1 slicer, Can SurveyName2 Slicer shows only this selected surveys? In this model it is not possible beceuse there is no relationship with Table (7) and Table (7a).

II- The Question Text filter should contain only the questions asked in the selected surveys.
In other words, since I selected survey1 and survey2 in the filter of surveyname1, it should bring the questions asked in these two surveys, and then only the questions asked in the survey2 survey because I selected survey2 in the surveyname2 filter.

Anonymous So, yes, you can create a relationship like what I have in the updated PBIX below.

Anonymous Let me know if this is solved or not. Spent a LOT of time on this one so want to close the loop if possible.

Greg_Deckler I know you spent a lot of timi fix this problem but It wasnt fixed.

As you see that if I select SurveyName1 filter survey1 and survey2 cohort show to us 2

BUT when I select SurveyName2 filter survey1 cohort show to us 3 so again it is not fixed we see same problem again.

I want to see this 2 users distribution in survey1. So cohort and pie chart must show us only two users distribution. And when I select surveys in SurveyName1 filter, SurveyName2 filter will be filtered same surveys in SurveyName1.

So I'm sorry but it is not fixed.

Greg_Deckler when I select survey1 and survey2 in SurveyName slicer, I can see the participationg users both this surveys 2.

But If I dont select any survey in SurveyName2 slicer, I can not see the distribution of this 2 users's answers for selecting questions

If I select a survey from the surveyName2 filter, I need to reach the distribution of these 2 users in that survey. If I do not select a survey from the surveyName2 filter, I should see the distribution of these two users according to the values ​​selected in the surveyName1 filter and selected in the QuestionText filter.