Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MD_POWER_BI
Frequent Visitor

Help with nested ifs/using switch

Slide1.PNGHaving 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...." 

3 REPLIES 3
MD_POWER_BI
Frequent Visitor

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

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



MFelix
Super User
Super User

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors