Use CALCULATE() without using expression

[Anonymous]

Hello people  🙂
First of all, here's my PBI file: test2 

I have a small problem in a calculated table that I created.
My standard table follows this structure:

COD	DATE	LEVEL
00	12/01/2020	M
00	12/03/2020	1H
11	12/03/2020	1H
11	12/05/2020	A
11	12/05/2020	A

As you can see, in the COD column the records can be repeated and the DATE column can have different dates for the same COD.
I would like to create a calculated table that shows all distinct and most recent CODs, and their respective LEVEL.
Based on the example table above, the new table would be somethign like this:

COD	DATE	LEVEL
00	12/03/2020	1H
11	12/05/2020	A

With this calculated table, I can get all  distinct and most recent CODs:

_newTable = SUMMARIZE('table'; 'table'[COD]; "LEVEL"; CALCULATE(MAX('table'[LEVEL]); ALLEXCEPT('table';'table'[COD])); "MAX VALUE"; CALCULATE(MAX('table'[DATE]);ALLEXCEPT('table';'table'[COD])))

The only problem in this calculated table is that the LEVEL column does not show the actual LEVEL for that COD and that DATE. It shows the highest order character on the alphabetical scale and associates it with the most recent COD. I found that the problem lies in that part of the code:

"LEVEL", CALCULATE(MAX('table'[LEVEL])

This brings the higher letter of that specific COD on the alphabetical scale (if the same COD has LEVEL A, B and Z, it will always show the letter Z). An example of this is analyzing the COD 40501455 (in the .pbix file). The most recent LEVEL of this COD is 1D, but the _newTable shows H because H is above a LEVEL that starts with a number on the alphabetic scale.
But if I just remove this MAX from the code, it points out a syntax error.
Can someone please help me?

[amitchandak · Accepted solution]

@Anonymous ,

Try like

filter(SUMMARIZE('table'; 'table'[COD]; 'table'[LEVEL]; 'table'[DATE],"max_dt"CALCULATE(MAX('table'[DATE]);ALLEXCEPT('table';'table'[COD])))
,[DATE]=[max_dt])

[Anonymous]

Thank you so much @amitchandak !!!!!
This is exactly what I want!

[Vvelarde]

@Anonymous 

Hi, try creating this DAX Table:

ResultTable =
SUMMARIZECOLUMNS (
    Table1[COD];
    "MAX"; MAX ( 'Table1'[Date] );
    "LEVEL"; CALCULATE (
        VALUES ( Table1[LEVEL] );
        FILTER ( Table1; Table1[DATE] = MAX ( Table1[DATE] ) )
    )
)

Regards

Victor