Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

AND Filtering in the report

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 bot...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

    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's avatar
Anonymous
Not applicable
5 years ago

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's avatar
Greg_Deckler
Community Champion
5 years ago

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.