Seperate table with only the Max Value For every unique combination of other Columns

[Anonymous]

Hello!

I have a table that start with a Name and a Type. They also have a date of entrt. A bit like this

Date entry	.	Name	Type	Other Data
01-01-2021	.	AAA	1
01-01-2022	.	AAA	1
02-01-2022	.	BBB	1
02-01-2022	.	BBB	2
01-01-2021	.	CCC	1
01-01-2022	.	CCC	1
01-01-2022	.	CCC	2

I need to make a table that only contains the rows that have the Max date of entry for all combinations of Name and Type.

I tried making a calctulated table that contained each unique combination of Name and Type, and then calculated what its max Date was. I was succesfull at this. Sadly i then noticed i was unable to use this calculated table in further Dax formules when making a new table. Why is this and is there a way around this?

Or is there a better way all together? 

The calculated table that i made would have looked like 

Name	Type	Max date Entry
AAA	1	01-01-2022
BBB	1	02-01-2022
BBB	2	02-01-2022
CCC	1	01-01-2022
CCC	2	01-01-2022

[Anonymous]

Hello @Jos_Woolley  and @tamerj1 

That Summerize function is indeed the function i use to create a table that only contains the highest Date for every combination. 

Now i need a table that contains all rows from the origenal table that have that Max Highest Date for every combination.

When i try to make a new table i try to write the following line:

HighestData = Filter("Data table",
And(And(
"Data table"[Name]="Max Date Table"[Name],
"Data table"[Type]="Max Date Table"[Type],
"Data table"[Entry Date]="Max Date Table"[Max Entry Date]
)))

But it says i cannot select this column from the Max Date Table

[tamerj1 · Accepted solution]

Hi @Anonymous 
Please try

HighestData =
VAR T1 =
    ADDCOLUMNS (
        'Data table',
        "Max Entry Date",
            CALCULATE (
                MAX ( 'Data table'[Date] ),
                ALLEXCEPT ( 'Data table', 'Data table'[Name], 'Data table'[Type] )
            )
    )
VAR T2 =
    SELECTCOLUMNS (
        T1,
        "Name", [Name],
        "Type", [Type],
        "Date", [Date],
        "Max Entry Date", [Max Entry Date]
    )
RETURN
    T2

[Anonymous]

Thanks @tamerj1 

Not exactly your solution, but based on your solution this formula gave me what i needed!

HighestData =
VAR T1 =
    ADDCOLUMNS (
        'Data table',
        "Max Entry Date",
            CALCULATE (
                MAX ( 'Data table'[Date] ),
                ALLEXCEPT ( 'Data table', 'Data table'[Name], 'Data table'[Type] )
            )
    )
RETURN
FILTER(T1, [Max Entry Date]='Data table'[Entry Date])

[tamerj1]

Hi @Anonymous 

Please use 

NeW Table =
SUMMARIZE (
    TableName,
    TableName[Name],
    TableName[Type],
    "Max Date", MAX ( TableName[Date] )
)

[Jos_Woolley]

Hi,

Can you clarify "i was unable to use this calculated table in further Dax formules when making a new table"? Why not? What message did you receive? And what was your DAX for generating your calculated table?

Regards