Forum Discussion
Anonymous
6 years agoNot applicable
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...
- 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
5 years agoNot applicable
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
Community Champion
5 years agoAnonymous No arguments over the AND slicer, it's a tricky problem and causes all sorts of headaches!