DAX IF formula

[DHB]

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.

[jennympho]

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. 

[Seth_C_Bauer]

@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]

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?

[Seth_C_Bauer]

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

[DHB]

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.

[Seth_C_Bauer · Accepted solution]

@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"
)))))))

[DHB]

That's awesome.  It works perfectly, thank you Eno1978.

[DHB]

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