For Each and Exists clause implementation in DAX

[Anonymous]

Hi,

Im trying to implement a complex logic that looks easy on algorith but tough when comes to implemenation.

Any help on M code or DAX side should be helpful

Algorithm:

if Executed Flag = 1 then
		(Only)if Title Flag 202 and 203 both exist for each Feature ID then
				(Only if User Story Status of both 202 and 203 Title Flags = "Complete or Closed" then
						"Workshop Completed"
	
						else "Workshop Executed but not completed" 
						// happens when Title Flags 202 or 203 either 
						//one exists or none exists for each Feature ID. 
						//Or any one or both of these 2 user story status = "Pending"
		else "Workshop Executed but not completed"
else if Execute Flag = 0 then "Not Executed"
//Note that workshop Status is at feature level and hence it can't be differnt for same feature id

Table

I have color coded different features with different colors for understanding the last column(output).

Feature ID	User Story ID	User Story Status	Title Flag	Executed Flag	Workshop Status(Output Column)
RFID001	USIDR4	Pending		1	Workshop Complete
RFID001	USIDR5	Complete or Closed	202	1	Workshop Complete
RFID001	USIDR6	Complete or Closed	203	1	Workshop Complete
RFID002	USIDR7	Complete or Closed		1	Workshop Not Complete
RFID002	USIDR8	Pending		1	Workshop Not Complete
RFID002	USIDR9	Complete or Closed	202	1	Workshop Not Complete
RFID002	USIDR10	Pending	203	0	Workshop Not Complete
RFID003	USIDR11	Complete or Closed	203	1	Workshop Executed but not completed
RFID004	USIDR12	Complete or Closed	203	0	Not Executed
RFID005	USIDR13	Complete or Closed	202	0	Not Executed

[PhilipTreacy]

@Anonymous 

Let me know if this issue is fixed with the file I modified for you to fix the text/integer comparison problem.

Regards

Phil

[Anonymous]

@tex628 did exactly what you did, with some minor corrections

Here is the DAX

Calculated Column =

VAR id1 = [Feature ID]

RETURN

IF (

[Executed Flag] = 1,

IF (

CALCULATE (

COUNTROWS ( 'Table' ),

'Table'[Feature ID] = id1,

OR ( 'Table'[Feature ID] = "203", 'Table'[Title Flag] = "203" ) = 2

),

IF (

CALCULATE (

COUNTROWS ( 'Table' ),

'Table'[Feature ID] = id1,

'Table'[User Story Status] = "Complete or Closed"

) = 2,

"Workshop Complete",

"Workshop Executed but not Completed"

),

"Workshop Executed but not Completed"

),

"Not Executed"

)

and here is the error

The expression contains multiple columns, but only a single column can be usedn a True False expressions that is used as atable filter calculation

[tex628]

Revised it a little bit. 

Give this a try:

Calculated Column = 
VAR id1 = [Feature ID]
RETURN
IF (
[Executed Flag] = 1,
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALL('Table'),
'Table'[Feature ID] = id1,
'Table'[Title Flag] IN {"202" , "203"}
) = 2,
IF (
CALCULATE (
COUNTROWS ( 'Table' ),
ALL('Table'),
'Table'[Feature ID] = id1,
'Table'[Title Flag] IN {"202" , "203"},
'Table'[User Story Status] = "Complete or Closed"
) = 2,
"Workshop Complete",
"Workshop Not Complete"),
"Workshop Executed but not Completed"),
IF(
    'Table'[User Story Status] = "Pending" && 
    CALCULATE (
COUNTROWS ( 'Table' ),
'Table'[Title Flag] IN {"202" , "203"}
) > 0 ,
"Workshop Not Complete" ,
"Not Executed")
)

[Anonymous]

@PhilipTreacy can you please help me with the logic

[Anonymous]

@PhilipTreacy I just want to make sure that

calculated column that I have created and the workshop status(output) match exactly.

[tex628]

Give this DAX column a try and let me know how it goes! 

Calculated Column = 
var id = [Feature ID]
Return
IF ( [Executed Flag] = 1 ,
IF ( CALCULATE( COUNTROWS( 'Table' ) , [Feature ID] = id, OR( [Title Flag] = 203 , [Title Flag] = 203] )) = 2 , 
IF ( CALCULATE( COUNTROWS( 'Table' ) , [Feature ID] = id, [User Story] = "Complete or Closed" ) = 2 , 
"Workshop Complete" , 
"Workshop Not Complete" , 
"Workshop Executed but not completed", 
"Not Executed" 
)))