Forum Discussion
Find the MAX by two groups.
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 |
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 .
6 Replies
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 .
CALCULATE(MAX(DateLast[ReadingDate.1]),ALLEXCEPT(DateLast,DateLast[Product1 ],DateLast[Facing.1]))Try use above dax to create calculated column
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.
DateLast is my Table, you should use tablename here instead of DateLast
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
