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,

[Greg_Deckler]

@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]

@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]

@Anonymous Try this one. Attached below.

[Anonymous]

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

[Greg_Deckler]

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

[Greg_Deckler]

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

[Anonymous]

@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]

@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]

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

[Greg_Deckler]

@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]

@Greg_Deckler I think we are so close to fix it.

As you can see it, If I select survey1 and survey2 in SurveyName1 filter, survey1 in SurveyName2 filter and qtext1 in QuestionText filter, The pie chart show us wrong data about q1a2text, It must be 1. Because this 2 users in survey1 answered qtext1 as q1a1text(2 users, ABC1 and ABC3) and q1a2text(1 user, ABC1) so pie chart show wrong data.

[Greg_Deckler · Accepted solution]

@Anonymous I looked at this, can't really fathom why that is happening. So, might look deeper but I think this fixes the problem, or at least works around it. PBIX is attached.

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 __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
    )

[Anonymous]

@Greg_Deckler It's approximatly done. But I have a question. Pie chart is okay but when I want to see it i matrix chart for surveyName, It show us wrong data. Why? and How can I fix it?

[Greg_Deckler]

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

[Anonymous]

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

[Greg_Deckler]

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

[Greg_Deckler]

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

[Greg_Deckler]

@Anonymous First question is AND slicer - https://community.powerbi.com/t5/Quick-Measures-Gallery/Patient-Cohort-AND-Slicer/m-p/391883#M130

I'm not sure of your second requirement, can you post expected output?