Create calculated field using MAXX with Multiple IF conditions

[rkottap]

Hi Team,

I need to create a caalculated field using MAX & IF conditins if text is matching in a columns (Issue Type & Label) then add the text value for all the ID's in each row.
Logic:
IF "Issue Type"="Feature" & "Label" = "Pilot_Phd" THEN "Pilot"
EleseIF
IF "Issue Type"="Feature" & "Label" = "Testing" THEN "Test"
ElseIF
IF "Issue Type"="Feature" & "Label" = "Check" THEN "Control"
Else
"BAU" ENDIF

Please see the attached screenshot for expected "Output" in column "D".

I have tried below DAX, but it's not giving the result for all the rows.

Please giude me to resolve this issue.

DAX:

Output =

VAR MaxProject =

SWITCH(

TableName [Issue Type]="Feature" && TableName [Label]="Pilot_Phd", "Pilot",

TableName [Issue Type]="Feature" && TableName [Label]="Testing", "Test",

TableName[Issue Type]="Feature" && TableName [Label]="Check", "Control",

TRUE(),

RETURN

BLANK())

IF(TableName [ID]=TableName[ID], CALCULATE (MAXX (

FILTER (TableName, TableName [ID]=TableName[ID]), MaxProject)), "BAU")


Screenshot:

[Anonymous]

Hi @rkottap ,

Thanks to @tamerj1  for the reply, please allow me to provide another insight:

1. Create a calculated column.

Output =
VAR MaxProject = SWITCH(
TRUE(),
'Table'[Issue Type] = "Feature" && 'Table'[Label] = "Pilot_Phd", "Pilot",
'Table'[Issue Type] = "Feature" && 'Table'[Label] = "Testing", "Test",
'Table'[Issue Type] = "Feature" && 'Table'[Label] = "Check", "Control")
RETURN

MaxProject

2. Create the calculated column again to get the final result.

Column =
VAR _out = CALCULATE(MAX('Table'[Output]),FILTER(ALL('Table'),'Table'[ID] = EARLIER('Table'[ID])))
RETURN
IF(_out = BLANK(),"BAU",_out)

3. You can click the little eye next to the column name to hide unnecessary columns.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tamerj1 · Accepted solution]

Hi @rkottap 

Please try

Output =
VAR CurrentIDTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR FeatureTypeRecord =
FILTER ( TableName, TableName[Issue Type] = "Feature" )
VAR FeatureLabel =
MAXX ( FeatureTypeRecord, TableName[Label] )
VAR Result =
SWITCH (
FeatureLable,
"Pilot_Phd", "Pilot",
"Testing", "Test",
"Check", "Control",
"BAU"
)
RETURN
Result

[rkottap]

I have added the VAR name in the second VAR line, where Table name is used;

Now it's working fine, Thank you for your help 🙂

Output =
VAR CurrentIDTable =
CALCULATETABLE ( TableName, ALLEXCEPT ( TableName, TableName[ID] ) )
VAR FeatureTypeRecord =
FILTER ( CurrentIDTable , TableName[Issue Type] = "Feature" )
VAR FeatureLabel =
MAXX ( FeatureTypeRecord, TableName[Label] )
VAR Result =
SWITCH (
FeatureLable,
"Pilot_Phd", "Pilot",
"Testing", "Test",
"Check", "Control",
"BAU"
)
RETURN
Result

[tamerj1]

@rkottap 
You are right. That was a typo mistake.

[rkottap]

Hi @tamerj1 
Thank you for your response.

It's not returning the values as per the conditions. 
It's adding text "BAU" for all the rows in table.