Create Calculated column based on Multiple IF Conditions and MAXX between two tables

[rkottap]

Hi,

I need to create a column for the below Tableau function using DAX between two tables?

Output =

{FIXED [Epic Link], [ABC Review]: MAX (

IF Table1[ABC Review] = "ABC1" THEN Table2 [Planning1]

ELSEIF Table1[ABC Review] = "ABC2" THEN Table2 [Planning2]

ELSEIF Table1[ABC Review] = "ABC3" AND Table2 [XYZ1 Date] > Table2 [ABC2 Date] THEN Table2 [XYZ1 Date]
ELSEIF Table1[ABC Review] = "ABC3" AND Table2 [XYZ1 Date] < Table2 [ABC2 Date] THEN Table2 [ABC2 Date]
ELSEIF Table1[ABC Review] = "ABC4" AND Table2 [XYZ2 Date] > Table2 [ABC3 Date] THEN Table2 [XYZ2 Date]
ELSEIF Table1[ABC Review] = "ABC4" AND Table2 [XYZ2 Date] < Table2 [ABC3 Date] THEN Table2 [ABC3 Date]
ELSEIF Table1[ABC Review] = "ABC5" AND Table2 [XYZ3 Date] > Table2 [ABC4 Date] THEN Table2 [XYZ3 Date]
ELSEIF Table1[ABC Review] = "ABC5" AND Table2 [XYZ3 Date] < Table2 [ABC4 Date] THEN Table2 [ABC4 Date]
END)}

[v-kongfanf-msft · Accepted solution]

Hi @rkottap ,

Maybe you can try to modify formula like below and create calculated column:

Output =
VAR MaxValue =
    MAXX (
        FILTER (
            Table2,
            Table2[Epic Link] = Table1[Epic Link]
                && Table2[ABC Review] = Table1[ABC Review]
        ),
        SWITCH (
            TRUE (),
            Table1[ABC Review] = "ABC1", Table2[Planning1],
            Table1[ABC Review] = "ABC2", Table2[Planning2],
            Table1[ABC Review] = "ABC3"
                && Table2[XYZ1 Date] > Table2[ABC2 Date], Table2[XYZ1 Date],
            Table1[ABC Review] = "ABC3"
                && Table2[XYZ1 Date] < Table2[ABC2 Date], Table2[ABC2 Date],
            Table1[ABC Review] = "ABC4"
                && Table2[XYZ2 Date] > Table2[ABC3 Date], Table2[XYZ2 Date],
            Table1[ABC Review] = "ABC4"
                && Table2[XYZ2 Date] < Table2[ABC3 Date], Table2[ABC3 Date],
            Table1[ABC Review] = "ABC5"
                && Table2[XYZ3 Date] > Table2[ABC4 Date], Table2[XYZ3 Date],
            Table1[ABC Review] = "ABC5"
                && Table2[XYZ3 Date] < Table2[ABC4 Date], Table2[ABC4 Date],
            BLANK ()
        )
    )
RETURN
    MaxValue

Best Regards,
Adamk Kong

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

[SaranKumarM]

Hi @rkottap 

could you please try with switch function as below

Result = SWITCH(TRUE(), MAX(Table1[ABC Review]) = "ABC1", Table2 [Planning1],

MAX(Table1[ABC Review]) = "ABC2", Table2 [Planning2],

MAX(Table1[ABC Review]) = "ABC3" && MAX(Table2 [XYZ1 Date]) > MAX(Table2 [ABC2 Date]), Table2 [XYZ1 Date],

MAX(Table1[ABC Review]) = "ABC3" && MAX(Table2 [XYZ1 Date]) < MAX(Table2 [ABC2 Date]), Table2 [ABC2 Date],

MAX(Table1[ABC Review]) = "ABC4" && MAX(Table2 [XYZ2 Date]) > MAX(Table2 [ABC3 Date]), Table2 [XYZ2 Date],

MAX(Table1[ABC Review]) = "ABC4" && MAX(Table2 [XYZ2 Date]) < MAX(Table2 [ABC3 Date]), Table2 [ABC3 Date],

MAX(Table1[ABC Review]) = "ABC5"&& MAX(Table2 [XYZ3 Date]) > MAX(Table2 [ABC4 Date]), Table2 [XYZ3 Date],

MAX(Table1[ABC Review]) = "ABC5" && MAX(Table2 [XYZ3 Date]) < MAX(Table2 [ABC4 Date]), Table2 [ABC4 Date])

Thanks!

[ryan_mayu]

could you pls provide the sample data and exptected output?