Forum Discussion
AND Filtering in the report
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 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.
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 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 No arguments over the AND slicer, it's a tricky problem and causes all sorts of headaches!
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?