Forum Discussion

MD_POWER_BI's avatar
MD_POWER_BI
Frequent Visitor
3 years ago

Help with nested ifs/using switch

Having difficulty with this function trying to suppress data.

 

I ahve to suppress student counts where n<10, except for school number "0A000A". But, Keep receiving this message:

 

"A single value for column 'LEAschool' ion the table 'School_Directory_DIM' cannot be determined.This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max...." 

Data Protection
Need Help

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    3 years ago

    Hi MD_POWER_BI ,

     

    In measure you need to use aggregated values or table functions in this case you need to wrap the School_Directory_DIM in a SELECTEDVALUE or a DISTINCT. that way you are abble to get the expected result.

  • MD_POWER_BI's avatar
    MD_POWER_BI
    Frequent Visitor
    3 years ago
    Student_Count_Suppressed =
        SWITCH(
            True(),
                sum(Student_Survey_Topic[Total Students Responded UnSupp])<10 && SELECTEDVALUE(School_Directory_DIM[LEASchool],"0A000A"), "*",
                sum(Student_Survey_Topic[Total Students Responded UnSupp])<10 && SELECTEDVALUE(School_Directory_DIM[LEASchool],"0A000A"), sum(Student_Survey_Topic[Total Students Responded UnSupp]),
                sum(Student_Survey_Topic[Total Students Responded UnSupp])>=10, sum(Student_Survey_Topic[Total Students Responded UnSupp]))
     
    This doesn't work still
    • MFelix's avatar
      MFelix
      Super User
      3 years ago

      Hi MD_POWER_BI 

       

      Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

      If the information is sensitive please share it trough private message.