Forum Discussion

DHB's avatar
DHB
Helper V
9 years ago
Solved

DAX IF formula

 

Hi all,

 

I’m trying to write a DAX formula to give me a new column with one of the following values based on the submission type;

submission_types

NEW COLUMN

Discussion topic

Discussion

Online upload

Plain Assignment

 

So far I’ve tried…

Column = IF((assigment_dim[submission_types] = "discussion_topic","Discussion", IF(assignment_dim[submission_types]= “Online upload” , “Plain Assignment”, "Other")))

 

It’s not working and I get this message…

To use special characters in a measure name, enclose the entire name in brackets ( [] ) and add a ] to any closing brackets in the name.

 

Can anyone tell me what I’m doing wrong? Thank you in advance for your help.

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    DHB I'm not aware of how to get around the first requirement, I'd just add the additional "IF". the second one searching for "media" in the value should be resolved using the added syntax I have below:

    Column =
    IF('assignment_dim'[submission_types] = "discussion_topic","Discussion",
    IF(assignment_dim[submission_types]= "online_upload", "Plain Assignment",
    IF(assignment_dim[submission_types]= "external_tool", "External Tool",
    IF(assignment_dim[submission_types]= "not_graded", "Ungraded",
    IF(assignment_dim[submission_types]= "online_quiz", "Quiz",
    IF(assignment_dim[submission_types]= "none", "Plain Assignment",
    IF((SEARCH("*media*", assignment_dim[submission_types],1,0)>0)= TRUE, "Media Content","Other"
    )))))))

8 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    DHB This should work

    Column = IF([submission_types] = "discussion topic","Discussion", IF([submission_types]= "Online Upload" , "Plain Assignment", "Other"))

     

    edit: with table names (was testing some things out and stripped them.

    Column = IF('assignment_dim'[submission_types] = "discussion topic","Discussion", IF(assignment_dim[submission_types]= "Online Upload" , "Plain Assignment", "Other"))

    • DHB's avatar
      DHB
      Helper V
      9 years ago

      Thank you for your quick response.  I'm getting this message now...

       

      A single value for column 'submission_types' in table 'assignment_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, count, or sum to get a single result.

       

      The submission types column has text values.  Could this be the issue here?

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        DHB You need to create a calculated column, it would appear you are trying to create a measure.

  • jennympho's avatar
    jennympho
    Regular Visitor
    8 years ago

    Hi there,

    I am new to the Power BI community.  I am trying to create a new calculated column using IF function but it said the IF function could not be recognized.  I am just trying to do 

    = IF([account]<4000,-[amount],[amount])

     

    however, it doesn't work.  Could anyone explain to me why it doesn't work? It's just a simple formula!

     

    Account and amount columns are both numeric - i am trying to chnage the amount to negative amount if it's within certain account range.