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

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

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

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