Forum Discussion

rkottap's avatar
rkottap
Frequent Visitor
2 years ago
Solved

Create calculated field using MAXX with Multiple IF conditions

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 "Iss...
  • tamerj1's avatar
    tamerj1
    2 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.