topic Create calculated field using MAXX with Multiple IF conditions in DAX Commands and Tips

Create calculated field using MAXX with Multiple IF conditions

rkottap — Mon, 29 Apr 2024 18:33:00 GMT

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:

Re: Create calculated field using MAXX with Multiple IF conditions

tamerj1 — Mon, 29 Apr 2024 18:47:46 GMT

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

Re: Create calculated field using MAXX with Multiple IF conditions

rkottap — Tue, 30 Apr 2024 06:37:29 GMT

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.

Re: Create calculated field using MAXX with Multiple IF conditions

rkottap — Tue, 30 Apr 2024 06:43:35 GMT

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

Re: Create calculated field using MAXX with Multiple IF conditions

Anonymous — Tue, 30 Apr 2024 07:44:46 GMT

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.

Re: Create calculated field using MAXX with Multiple IF conditions

tamerj1 — Tue, 30 Apr 2024 14:00:41 GMT

rkottap
You are right. That was a typo mistake.