Forum Discussion
Question regarding Box and Whisker filtering
This question from stack overflow:
http://stackoverflow.com/questions/40915576/power-bi-box-and-whisker-filter-with-an-if-statement.
Is it possible to run a filtering Dax formula through a Box and Whisker plot?
So If I only want a Box and Whisker for values where distinct count is greater than 4, only those values will show up regardless of visual filters.
JPP was able to answer the question to completeness, on SO: http://stackoverflow.com/questions/40915576/power-bi-box-and-whisker-filter-with-an-if-statement/40931563#40931563
He used the following formula: Filter Score = IF(CALCULATE(DISTINCTCOUNT(Courses[Score]), ALLEXCEPT(Courses, Courses[Course]))>4, MIN(Courses[Score]), BLANK())
Which solved the issue, but if you want to make sure the measure reacts to filters on the page, you need to add to the ALLEXECPT portion of the formula.
So the final formula --- if you were to implement this in your own dataset --- probably looks more like the following:
Filter Score = IF(CALCULATE(DISTINCTCOUNT(Courses[Score]), ALLEXCEPT(Courses, Courses[Course], Courses[Session]))>4, MIN(Courses[Score]), BLANK())
3 Replies
Hi jpf5046,
Based on my test, it's not supported to use Filter function within a measure (eg: Distinct count score = CALCULATE(AVERAGE(Courses[Score]),FILTER(Courses,DISTINCTCOUNT(Courses[Score])>4)) ) then use this measure to display values in a Box and Whisker chart. For this issue, I would suggest you contact the author of this custom visual for help. You can send the email to this address: [email protected].
Besides, I agree with Leonard's points in the stack overflow. To work around the issue, please create a calculated column to return distinct count of score for each course, then use this new column in Visual Lvel filter to filter values for the chart.
DistinctCount = CALCULATE(DISTINCTCOUNT(Courses[Score]),FILTER(Courses,EARLIER( Courses[Course] ) = 'Courses'[Course] ))
Best Regards,
Qiuyun YuJPP was able to answer the question to completeness, on SO: http://stackoverflow.com/questions/40915576/power-bi-box-and-whisker-filter-with-an-if-statement/40931563#40931563
He used the following formula: Filter Score = IF(CALCULATE(DISTINCTCOUNT(Courses[Score]), ALLEXCEPT(Courses, Courses[Course]))>4, MIN(Courses[Score]), BLANK())
Which solved the issue, but if you want to make sure the measure reacts to filters on the page, you need to add to the ALLEXECPT portion of the formula.
So the final formula --- if you were to implement this in your own dataset --- probably looks more like the following:
Filter Score = IF(CALCULATE(DISTINCTCOUNT(Courses[Score]), ALLEXCEPT(Courses, Courses[Course], Courses[Session]))>4, MIN(Courses[Score]), BLANK())
Hi jpf5046,
Glad to hear the issue has been solved. :smileyhappy: Would you please mark a helpful reply as an answer, so that we can close the thread?
Best Regards,
Qiuyun Yu
