Forum Discussion

rkottap's avatar
rkottap
Frequent Visitor
2 years ago
Solved

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

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)}

General Comment
Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
    SaranKumarM
    Frequent Visitor
    2 years ago

    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!