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 Di...
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"
    )))))))

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.

    • DHB's avatar
      DHB
      Helper V
      9 years ago

      You're right.  It's all working pefectly now.  I've just got to build it up to cover some other submission types.

       

      thanks so much for your help.

       

      DHB

    • DHB's avatar
      DHB
      Helper V
      9 years ago

      I've now built the following formula which works, thanks to your input Eno1978.

       

      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","Other"))))))

       

      A couple of questions though;

      I've asked for "online_upload" and "none" to both read as "Plain Assignment" in the new column.  Is there a way I can write "online_upload" OR "none" in the single IF or do I have to write an IF for each as I have above?

       

      I want "Media Content" in the new column for all vlaues in the first column that contain the word "media".  How can I incorporate this?

       

      thanks again.

      • Anonymous's avatar
        Anonymous
        Not applicable
        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"
        )))))))