Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

Calculated Column with multiple AND & OR

Hi, I need to create a calculated column with the following rules: Write 1 IF: Project is within G0 to G5 and status is 1 and description field is blank OR (Project is within G0 to G5 and ...

Calculated columns

DAX

SpartaBI

4 years ago

Anonymous try this:

Overall Status =
IF(
	Initiatives[Stage] 
		IN {
			"Demand Qualification", 
			"G0 Approval", 
			"G1 Approval", 
			"G2 Approval",
			"G3 Approval",
			"G4 Approval",
			"G5 Approval"
		},
		IF(
			Initiatives[Status] = "0", 
			1,
			IF(
				Initiatives[Status] IN {"1","2"}
					&& Initiatives[Description] = BLANK().
				1,
				0
			)
		)
)

Showcase Report – Contoso By SpartaBI

SpartaBI

Community Champion

4 years ago

Anonymous try this:

Overall Status =
IF(
	Initiatives[Stage] 
		IN {
			"Demand Qualification", 
			"G0 Approval", 
			"G1 Approval", 
			"G2 Approval",
			"G3 Approval",
			"G4 Approval",
			"G5 Approval"
		},
		IF(
			Initiatives[Status] = "0", 
			1,
			IF(
				Initiatives[Status] IN {"1","2"}
					&& Initiatives[Description] = BLANK().
				1,
				0
			)
		)
)

Showcase Report – Contoso By SpartaBI

Anonymous
Not applicable
4 years ago
Hi Sparta,

Thanks for you reply.
When I try the provided solution I get the following error message:
Function 'CONTAINSROW' does not support comparing values of type Integer with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values.

Any idea of how to get around that ?
- SpartaBI
  Community Champion
  4 years ago
  I think because your status column is a text, I'm adding "" around the relevant places in the original messages, you will see this in a minute
  - Anonymous
    Not applicable
    4 years ago
    You were right with the status code column beeing text.
    But still think there are something wrong with you code.
    E.g. a project with Initiatives[Stage] = G2 Approval and
    Initiatives[Status] = 1 and Describtion BLANK will be assigned with 0