Forum Discussion

gill_thornton's avatar
gill_thornton
Helper I
2 years ago
Solved

Measure using switch giving error on visual

Please can someone help with the following.  I have a measure:

ontext = SWITCH(TRUE(),
                    SELECTEDVALUE(Context[Gender])="M","Boys",
                    SELECTEDVALUE(Context[Gender])="F","Girls",
                    SELECTEDVALUE(Context[EAL]) = "Unknown","Unclassified EAL",
                    SELECTEDVALUE(Context[EAL]) = "No","English",
                    SELECTEDVALUE(Context[EAL]) = "Yes","Other than English",
                    SELECTEDVALUE(Context[Ethnicity])="Any other Asian background","Asian - Any other Asian background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other Black background","Black - Any other Black background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other ethnic group","Other - Any other ethnic group",
                    SELECTEDVALUE(Context[Ethnicity])="Any other mixed background","Mixed - Any other mixed background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other White background","White - Any other White background",
                    SELECTEDVALUE(Context[Ethnicity])="Bangladeshi","Asian - Bangladeshi",
                    SELECTEDVALUE(Context[Ethnicity])="Black African","Black - Black African",
                    SELECTEDVALUE(Context[Ethnicity])="Black Caribbean","Black - Caribbean",
                    SELECTEDVALUE(Context[Ethnicity])="Chinese","Chinese Total",
                    SELECTEDVALUE(Context[Ethnicity])="Indian","Asian - Indian",
                    SELECTEDVALUE(Context[Ethnicity])="Information Not Yet Obtained","Unclassified - Total",
                    SELECTEDVALUE(Context[Ethnicity])="Pakistani","Asian - Pakistani",
                    SELECTEDVALUE(Context[Ethnicity])="Refused","Unclassified - Total",
                    SELECTEDVALUE(Context[Ethnicity])="Traveller of Irish heritage","White - Traveller of Irish heritage",
                    SELECTEDVALUE(Context[Ethnicity])="White - British","White - White British",
                    SELECTEDVALUE(Context[Ethnicity])="White and Asian","Mixed - White and Asian",
                    SELECTEDVALUE(Context[Ethnicity])="White and Black African","Mixed - White and Black African",
                    SELECTEDVALUE(Context[Ethnicity])="White and Black Caribbean","Mixed - White and Black Caribbean",
                    OR(SELECTEDVALUE(Context[Ethnicity])="White - Irish",SELECTEDVALUE(Context[Ethnicity])="White - Northern Irish"),"White - White Irish",
                    OR(SELECTEDVALUE(Context[Ethnicity])="Other Gypsy/Roma",SELECTEDVALUE(Context[Ethnicity])="Any other ethnic group"),"Other - Any other ethnic group",
                    OR(SELECTEDVALUE(Context[Ethnicity])="Gypsy",SELECTEDVALUE(Context[Ethnicity])="Gypsy/Roma"),"White - Gypsy/Roma",
                    SELECTEDVALUE(Context[Pupil Premium Indicator]) ="T","Disadvantaged",
                    SELECTEDVALUE(Context[Pupil Premium Indicator]) ="F","Disadvantaged all other",
                    SELECTEDVALUE(Context[SEN Code])="E","SEN State EHC",
                    SELECTEDVALUE(Context[SEN Code])="K","SEN Supp",
                    SELECTEDVALUE(Context[SEN Code])="N","No SEN",
                    "Total")
 
This is causing an error in the visual - 'Calculation error in measure ...cannot convert value 'Total' of type text to type true/false'
 
Any help with this issue would be much apprecitated.  Thank you.
Need Help
  • gill_thornton's avatar
    gill_thornton
    2 years ago

    Thank you for taking the time to look at the issue.  I now have a solution for this.

4 Replies

  • gill_thornton's avatar
    gill_thornton
    Helper I
    2 years ago

    Thank you for taking the time to look at the issue.  I now have a solution for this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi gill_thornton ,

    The error message you’re seeing indicates that the function in your measure is returning a value of type text instead of a value of type true/false. This is because the last argument of your function is the text string , which is not a valid value for a true/false expression.

    ontext = SWITCH(TRUE(),
                    SELECTEDVALUE(Context[Gender])="M","Boys",
                    SELECTEDVALUE(Context[Gender])="F","Girls",
                    SELECTEDVALUE(Context[EAL]) = "Unknown","Unclassified EAL",
                    SELECTEDVALUE(Context[EAL]) = "No","English",
                    SELECTEDVALUE(Context[EAL]) = "Yes","Other than English",
                    SELECTEDVALUE(Context[Ethnicity])="Any other Asian background","Asian - Any other Asian background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other Black background","Black - Any other Black background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other ethnic group","Other - Any other ethnic group",
                    SELECTEDVALUE(Context[Ethnicity])="Any other mixed background","Mixed - Any other mixed background",
                    SELECTEDVALUE(Context[Ethnicity])="Any other White background","White - Any other White background",
                    SELECTEDVALUE(Context[Ethnicity])="Bangladeshi","Asian - Bangladeshi",
                    SELECTEDVALUE(Context[Ethnicity])="Black African","Black - Black African",
                    SELECTEDVALUE(Context[Ethnicity])="Black Caribbean","Black - Caribbean",
                    SELECTEDVALUE(Context[Ethnicity])="Chinese","Chinese Total",
                    SELECTEDVALUE(Context[Ethnicity])="Indian","Asian - Indian",
                    SELECTEDVALUE(Context[Ethnicity])="Information Not Yet Obtained","Unclassified - Total",
                    SELECTEDVALUE(Context[Ethnicity])="Pakistani","Asian - Pakistani",
                    SELECTEDVALUE(Context[Ethnicity])="Refused","Unclassified - Total",
                    SELECTEDVALUE(Context[Ethnicity])="Traveller of Irish heritage","White - Traveller of Irish heritage",
                    SELECTEDVALUE(Context[Ethnicity])="White - British","White - White British",
                    TRUE())

    SWITCH function (DAX) - DAX | Microsoft Learn

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

     

     

    • gill_thornton's avatar
      gill_thornton
      Helper I
      2 years ago

      Thank you.  The national data supplied by the department of education doesn't quite match the context data for our school population.  In the national data there is a value under characteristics of total.  I don't have such a value in my context table.  However, I need total to be shown as "All students".