Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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/409...
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,
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: mail@jppp.org.
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 Yu
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/409...
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. Would you please mark a helpful reply as an answer, so that we can close the thread?
Best Regards,
Qiuyun Yu
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |