How to take highest value of text column in subtotals in matrix.

[darshaningale]

I have the below data.

Order	Product	Order Date
1	P1	9-Oct
1	P2	10-Oct
1	P3	8-Oct
2	P4	9-Oct
2	P5	8-Oct

I want to show the Product which is present in the row where the date is latest.

Result

Order	Product	Order Date
1	P2	10-Oct
2	P4	9-Oct

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

 

[darshaningale]

Thank you @Ashish_Mathur ..the solution works fine..

We have to make one measure for farthest date.

and another one for the product column to select the product of the farthest date.

[Ashish_Mathur]

You are welcome.

[darshaningale]

@Ashish_Mathur the link doesnt work for me.

[Ashish_Mathur]

Hi,

It works just fine.  Please try agin.

[darshaningale]

@Ashish_Mathur i got it now..seems to be a VPN problem at my side..I need to install the latest version of PBI..so it will be a while I will get back to you.

[Anonymous]

Hi @darshaningale 

Create a column

_Selected =IF('table'[Order Date] =  CALCULATE(MAX('table'[Order Date]), ALLEXCEPT('table','table'[Order])),1,0)

And while displying the result filter the result on _selected to have 1.

[darshaningale]

@Anonymous we do not want to filter any data here..we just want to consider latest value of order date and show that product in subtotals.

[Anonymous]

HI @darshaningale 

The code will do as mentioned in the intial ask you have posted.

Maybe elaborate your ask more to get the exact solution.

[VijayP]

@darshaningale 

Your question is not clear

in case of P1 also of 9 oct then how to categorise only P4 as 9 Oct. any logic behind it?

[darshaningale]

@VijayP I have listed only the date part here..the actual data contains time part also.

if we have same timestamp , it should take alphanumeric ascending order of Product column.