Power BI Exchange

View Only

Conditional visualization depending on filter

• 1.  Conditional visualization depending on filter

Posted Apr 27, 2022 06:39 PM

I have a requirement when I display race at the state level to display all races and % of students for the race.  If the filter is other than the state level, I am supposed to display the top 1 race and the %.

I have all the calculations working except when I try to return the race value.

Calculations

Determine # and % of Students and race for the Top race

top1race = TopN (1,Values( StudentCourseDetails[Race]),StudentCourseDetails[# Distinct Students])
top1racevalue = CALCULATE([# Distinct Students], TOPN(1, VALUES('StudentCourseDetails'[Race]), [# Distinct Students], DESC))
% Top1Race = DIVIDE([Top1RaceValue] , [# Distinct Students])

Determine which % and value to use on visualization

Race% = if (IScrossFILTERED(DistrictSchoolSummary[CountyName]), StudentCourseDetails[% Top1Race],StudentCourseDetails[% of Distinct Students])
**RaceCalcValue = if (IScrossFILTERED(DistrictSchoolSummary[CountyName]), StudentCourseDetails[Top1Race],value(StudentCourseDetails[Race]))

** Error

A single value for column 'Race' in table 'StudentsCourseDetails' cannot be determined.  This can happen when a measure formula refers to a column that contains many values without specificing an aggregation such a min, max, count, or sum to get a single result.

And ideas on how to get the correct race values for each condition?  If this can be done with a calculation, is there a way to do like a conditional block which would display the correct visualization base on the condition?

Thanks!

------------------------------
Jo Marie Sellner

------------------------------