3 column value check to get status

[Anonymous]

Hi,

I am trying the below formula to get SLA status by checking values from 3 columns.

SLA_Status = IF( AND('Data'[04.OpenClosed_Staus]="Closed",AND(AND('Data'[Status]="Completed in Data Entry",'Data'[Status]="Rejected"),'Data'[03A.DFM_Ageing_Calc]>3)),"Dependency",blank())

Not getting any row having final values as "Dependency". Is the formula correct or can anyone help me on this?

Murali

[drewlewis15 · Accepted solution]

Replace the "AND" statement with an "OR" statement when checking the status.

Before:

SLA_Status = 
IF(
	AND(
		'Data'[04.OpenClosed_Staus]="Closed",
		AND(
			AND(
				'Data'[Status]="Completed in Data Entry",
				'Data'[Status]="Rejected"
			),
			'Data'[03A.DFM_Ageing_Calc]>3
		)
	),
	"Dependency",
	blank()
)

After:

SLA_Status = 
IF(
	AND(
		'Data'[04.OpenClosed_Staus]="Closed",
		AND(
			OR(
				'Data'[Status]="Completed in Data Entry",
				'Data'[Status]="Rejected"
			),
			'Data'[03A.DFM_Ageing_Calc]>3
		)
	),
	"Dependency",
	blank()
)

[Greg_Deckler]

You have a logical impossibility in your formula. Your formula, written so that it is readable is:

SLA_Status = 
IF(
	AND(
		'Data'[04.OpenClosed_Staus]="Closed",
		AND(
			AND(
				'Data'[Status]="Completed in Data Entry",
				'Data'[Status]="Rejected"
			),
			'Data'[03A.DFM_Ageing_Calc]>3
		)
	),
	"Dependency",
	blank()
)

It is a logical impossibility that your 'Data'[Status] field could ever have a value of "Completed in Data Entry" AND "Rejected".

It is often the case that good formatting makes problems obvious. Use Alt-Enter for new lines and tabs in the DAX editor to make things readable.

[Anonymous]

Is there any other posibilities on this logic?

[Anonymous]

Enclosed is the data sample. Need to get the SLA status column by Dax formula.

04.OpenClosed_Staus                Status                 Ageing    SLA status
Closed                        Completed in Data Entry    >3          Dependency
Closed                        Completed in Data Entry    0-3         Met
Closed                        Completed in Workflow     >3          Miss
Closed                        Completed in Workflow     0-3         Met
Closed                        Rejected                             >3          Dependency
Closed                        Rejected                             0-3         Met
Closed                        Rejected from Workflow    >3          Miss
Closed                        Rejected from Workflow    0-3         Met

[drewlewis15 · Accepted solution]

Replace the "AND" statement with an "OR" statement when checking the status.

Before:

SLA_Status = 
IF(
	AND(
		'Data'[04.OpenClosed_Staus]="Closed",
		AND(
			AND(
				'Data'[Status]="Completed in Data Entry",
				'Data'[Status]="Rejected"
			),
			'Data'[03A.DFM_Ageing_Calc]>3
		)
	),
	"Dependency",
	blank()
)

After:

SLA_Status = 
IF(
	AND(
		'Data'[04.OpenClosed_Staus]="Closed",
		AND(
			OR(
				'Data'[Status]="Completed in Data Entry",
				'Data'[Status]="Rejected"
			),
			'Data'[03A.DFM_Ageing_Calc]>3
		)
	),
	"Dependency",
	blank()
)

[Anonymous · Accepted solution]

Thanks Drewlewis. It worked.

[drewlewis15]

I would create a custom column using the query editor.  The structure of the statement using M would be:

if COLUMN1 = value1 and COLUMN2 = value2 and COLUMN3 = value3 then "Dependency" else null