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 "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:

  • 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

5 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    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

    • rkottap's avatar
      rkottap
      Frequent Visitor
      2 years ago

      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

    • rkottap's avatar
      rkottap
      Frequent Visitor
      2 years ago

      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. 

  • 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.