Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 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.

    All" data-title="Left Chart of All Counties, Right chart for Counties <> All" width="100" data-modalsrc="https://higherlogicdownload.s3.amazonaws.com/PBIUSERGROUP/UploadedImages/TLNClSIRcEGBKfYleigx_Screenshot 2022-04-27 173158.jpeg" data-imgbase="https://higherlogicdownload.s3.amazonaws.com/PBIUSERGROUP/UploadedImages/TLNClSIRcEGBKfYleigx_Screenshot 2022-04-27 173158.jpeg" data-imgthumbnail="https://higherlogicdownload.s3.amazonaws.com/PBIUSERGROUP/UploadedImages/TLNClSIRcEGBKfYleigx_Screenshot 2022-04-27 173158-T.jpeg" data-imgmedium="https://higherlogicdownload.s3.amazonaws.com/PBIUSERGROUP/UploadedImages/TLNClSIRcEGBKfYleigx_Screenshot 2022-04-27 173158-M.jpeg" data-imglarge="https://higherlogicdownload.s3.amazonaws.com/PBIUSERGROUP/UploadedImages/TLNClSIRcEGBKfYleigx_Screenshot 2022-04-27 173158-L.jpeg">


    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?


    Jo Marie Sellner