Find the MAX by two groups.

[Anonymous]

I have a data similar to this and i want to find the max ReadingDate for unique facing and product. For example, Cheetos chrunchy max date for facing.1 will be the result table below. This will be repeated for all products available. I want this created as a new column in the data such that i can use this new MaxReadinDate column as my new date field to create a "table visualization" in power bi with the other column of my choice in the new table. These other columns are not included here. Please, i can do this in power query but i prefer this is done in DAX. Thanks for your help.

Data

Desired result

ReadingDate	product	Facing
7/14/2020 11:19:25 AM	Cheetos Crunchy	1
7/13/2020 1:30:06 PM	Cheetos Crunchy	2

[Icey]

Hi @Anonymous ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

[amitchandak · Accepted solution]

@Anonymous , the second row does not seem correct

 You can have formula like

lastnonblankvalue(Table[Date],max(Table([Facing])))
or
lastnonblankvalue(Table[Date],sum(Table([Facing])))

You can use SUMMARIZECOLUMNS to create the table .

[FarhanAhmed]

CALCULATE(MAX(DateLast[ReadingDate.1]),ALLEXCEPT(DateLast,DateLast[Product1 ],DateLast[Facing.1]))

Try use above dax to create calculated column

[Anonymous]

@FarhanAhmed 

Thanks for your prompt response, i don't think it likes the code. It created the column but with an error, I t looks to me that the complain is about the "DateLast". My table name is merge.1, at what point does the table name come in, in your code? I ask this because i don't see where it should be included here, I am new to DAX so please pardon me.

[Anonymous]

It worked but not what i want. After dragging the field to the converse and selected other fields, it threw an error. Remember the reason for this is to be able to drag the field alongside others to create a table visualization.  All i want is to have a new column where this MaxDate is extracted so that instead of using the initial ReadingDate.1 field as my date field for the table visualization that i want to create, i would us ethe new MaxDate instead as my date field. Thanks

[FarhanAhmed]

DateLast is my Table, you should use tablename here instead of DateLast