Use MAX () in distinct values

[Anonymous]

Hello everyone,

I have a table in the structure below:

COD	DATE
00	12/01/2020
00	12/03/2020
11	12/03/2020
11	12/05/2020
11	12/05/2020

As the values ​​of the "COD" column can be repeated, I would like a measure that creates a new table with only the MAX dates of each COD. The result should be a table like this:

COD	DATE
00	12/03/2020
11	12/05/2020

Can someone hel me please?

[amitchandak]

@Anonymous , In case you need new table

New table = summarize(Table, Table[COD], "MaxDate",Max(Table[Max Date]))

Any visual you plot with row/group as cod, ,Max(Table[Max Date]) measure will give max date

[Anonymous]

Hello and thanks for the reply!

This measure works very well, as well as @camargos88 response.
but my original table has a column called LEVEL too. Just like that:

COD	DATE	LEVEL
00	12/01/2020	A
00	12/03/2020	B
11	12/03/2020	B
11	12/05/2020	C
11	12/05/2020	C

In case I want to show, in the new table, the column LEVEL, this measure return something like this:

COD	DATE	LEVEL
00	12/03/2020	A
00	12/03/2020	B
11	12/05/2020	B
11	12/05/2020	C

I could get the MAX DATE for each COD, but if the LEVEL column has different values, the table shows all those values, associating them with the MAX DATE. Just like the table above.

I wanted the table to be like this:

COD	DATE	LEVEL
00	12/03/2020	B
11	12/05/2020	C

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Anonymous]

Hey @Ashish_Mathur  thanks for the answer!

I downloaded your file and it is working correctly, but adapting your two measures with my file, this table created in design mode does not show any data when I insert the measure [Measure].

• I insert COD and the table shows all CODs normally
• I insert the measure [Max date] and the table shows all the unique CODs for each MAX DATE
• But when I insert the measure [Measure] the table shows a visual error.   😞

All I want to do with all these measures and tables is to be able to count all the different rows of each COD, but only the rows that the DATE field is the most recent AND showing the LEVEL of that special row too.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.  Clearly show where the problem is.

[Anonymous]

Hi @Ashish_Mathur !

Here is my .pbix file with your measures inserted: https://1drv.ms/u/s!Ak__XVgRMvymhFXJYKI2XgXpoVCx?e=aJtQ3y 

I also created a second table called "_newTable" using @V-pazhen-msft code:

_newTable = 
SUMMARIZE('table', 'table'[COD], 
"Level", CALCULATE(MAX('table'[LEVEL]), ALLEXCEPT('table','table'[COD])),
"MaxValue", CALCULATE(MAX('table'[DATE]), ALLEXCEPT('table','table'[COD])))

His code creates a new table with only the MAX (DATE) of each distinct value. It's almost right, the only problem is the:

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.
I didn't want that, I would the table to simply show the COD, its respective LEVEL based on the most recent DATE of each COD.

[Ashish_Mathur]

Hi,

You may refer to my solution at this link.

Hope this helps.

[V-pazhen-msft · Accepted solution]

@Anonymous 

If you only want group by COD, you should not include LEVEL as a group column nor as filter. 

Try make a little change:

Table 2 = 
SUMMARIZE('Table', 'Table'[COD], 
"Level", CALCULATE(MAX('Table'[LEVEL]), ALLEXCEPT('Table','Table'[COD])),
"MaxValue", CALCULATE(MAX('Table'[DATE]), ALLEXCEPT('Table','Table'[COD])))

 
Paul Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks for the answer @V-pazhen-msft ! 🙂
Your measure made it now show only one row for each COD, which is what I want.
But there is only one thing, in the LEVEL column the letters that appear for each COD are not related to the MAX DATE of the original table, but to the alphabetic order of that letter. I found that the problem is in this MAX of your measure:

Level", CALCULATE(MAX('Table'[LEVEL])

 
With this, the LEVEL column of Table 2 is comparing the different letters of the same COD and showing only the higher letter based in the alphabetical order. I did a test changing this MAX to MIN and concluded that this is what is happening. I tried to remove this MAX from LEVEL, but the measure points to a syntax error.

[Ashish_Mathur]

Hi,

To your Table visual, drag the COD column to the row labels and write this measure

=MAX(Data[Date])

Hope this helps.

[camargos88]

Hi @Anonymous ,

Try this code to create a new table:

T2 = SUMMARIZE('Table (2)'; 'Table (2)'[COD]; "MaxValue"; CALCULATE(MAX('Table (2)'[DATE]); ALLEXCEPT('Table (2)'; 'Table (2)'[COD])))

Ricardo

[Anonymous]

You are awesome! Thank you very much!! 🙂

Just one thing, when I asked the question, I showed only two columns in my table because I thought it would not be necessary to show the others, but my table does not have only two columns. However, using this measure that you created, everything worked very well, except for one of the columns that I didn't show previously.
My table is like this:

COD	DATE	LEVEL
00	12/01/2020	A
00	12/03/2020	B
11	12/03/2020	B
11	12/05/2020	C
11	12/05/2020	C

Your measure works well, so I made a small change to it to include the LEVEL column in the new table as well:

T2 = SUMMARIZE('Table (2)'; Table(2)'[LEVEL]; 'Table (2)'[COD]; "MaxValue"; CALCULATE(MAX('Table (2)'[DATE]); ALLEXCEPT('Table (2)'; 'Table (2)'[COD];Table(2)'[LEVEL])))

but the result was this:

COD	DATE	LEVEL
00	12/03/2020	A
00	12/03/2020	B
11	12/05/2020	B
11	12/05/2020	C

With your measure, I can get the MAX DATE for each COD, but if the LEVEL column has different values, the table shows all those values, associating them with the MAX DATE. Just like the table above.

I wanted the table to be like this:

COD	DATE	LEVEL
00	12/03/2020	B
11	12/05/2020	C