create tabel which will show the latest not null (if possible) tabel data from existing tabels

[Anonymous]

I have 2 tabels with same coulmn names :

Tabel 1 ---

Tabel 2 ---

I need to create tabel 3 which will show the latest not null (if possible) tabel data . If possible means for that particular row , if it can find the MOST RECENT not null value then put that else let it remain null  if it couldn't find any not null data in any of the tabel  for that particular row .

So my final tabel 3 should look like this  ---

[NaveenGandhi · Accepted solution]

Hello @Anonymous 

If appending the table1 and table2 is ok with you, Implement the below solution.

Creat table 3 using the below DAX.

Table 3 =
VAR table_3 =
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[TestCaseName],
            Table1[Date of Execution],
            Table1[Status]
        ),
        Table1[Date of Execution]
            = CALCULATE (
                MAX ( Table1[Date of Execution] ),
                ALLEXCEPT ( Table1, Table1[TestCaseName] )
            )
    )
RETURN
    table_3

This would give you the expected result.

If appeding is something you do not want to do, Let me know i will tweak the DAX accordingly.

Regards

Naveen

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

[NaveenGandhi · Accepted solution]

Hello @Anonymous 

If appending the table1 and table2 is ok with you, Implement the below solution.

Creat table 3 using the below DAX.

Table 3 =
VAR table_3 =
    FILTER (
        SUMMARIZE (
            Table1,
            Table1[TestCaseName],
            Table1[Date of Execution],
            Table1[Status]
        ),
        Table1[Date of Execution]
            = CALCULATE (
                MAX ( Table1[Date of Execution] ),
                ALLEXCEPT ( Table1, Table1[TestCaseName] )
            )
    )
RETURN
    table_3

This would give you the expected result.

If appeding is something you do not want to do, Let me know i will tweak the DAX accordingly.

Regards

Naveen

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

[milanpasschier3]

Nice solution. In terms of readability I would prefer:

Filtered table = 

SUMMARIZE('Appended table',
    'Appended table'[TestCaseName],
    "Most recent date of Execution",  MAX('Appended table'[Date of Execution]),
    "Status", CALCULATE(MAX('Appended table'[Status]),
        'Appended table'[Date of Execution] = MAX('Appended table'[Date of Execution]),
        'Appended table'[TestCaseName] = EARLIER('Appended table'[TestCaseName])
    )
)

[Anonymous · Accepted solution]

Thank you so much for the help !! This is exactly what I wanted .Appending the tabels is ok to me as I wanted to put dynamic values only at one place and that is happening now only while doing an append .The rest of the DAX formula is common irrespective of how many tabels I'm appending .I accepted it as the solution.

[milanpasschier3]

Hey man, can you hand the tables in as csv or excel or the table format included in the wysiwyg editor? Makes things easier to have a look. Best, Milan

[Anonymous]

Edited my question with the excel snapshot of the sample data .. Thank you ..Appreciate your time !!