topic Seperate table with only the Max Value For every unique combination of other Columns in DAX Commands and Tips

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

Anonymous — Tue, 26 Jul 2022 11:15:21 GMT

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

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

Jos_Woolley — Tue, 26 Jul 2022 11:28:23 GMT

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

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

tamerj1 — Tue, 26 Jul 2022 11:28:39 GMT

Hi Anonymous

Please use

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

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

Anonymous — Tue, 26 Jul 2022 11:40:38 GMT

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

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

tamerj1 — Tue, 26 Jul 2022 12:01:22 GMT

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

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

Anonymous — Tue, 26 Jul 2022 12:19:11 GMT

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