Forum Discussion
Autofill null value based on one column data - using dax calculated column
Dear all,
Currently my data base looks like the picture below. with many empty row, each items have a serial no. attached.
I want value to look like the desired output column.
I have searched for an answer in a different thread, using calculate (max(datacurrent), filter(table, item = earlier (items).
But the output I get look like the current output column.
Thank you for your help.
Hi,
This calculated column formula works
Column = LOOKUPVALUE(Data[Data current],Data[Serial],CALCULATE(MIN(Data[Serial]),FILTER(Data,Data[ITEM]=EARLIER(Data[ITEM])&&Data[Serial]>=EARLIER(Data[Serial])&&Data[Data current]<>BLANK())),Data[ITEM],Data[ITEM])Hope this helps.
4 Replies
Does it have to be DAX?
In Power Query you'd right click on the Data current column header and then Fill -> Up
Regards
Phil
Hi Phil,
Thank you for your reply.
Unfortunately, I do not have access to the server to use a solution under power query. I can only process data on the surface using dax.
Moreover, my data does not sort clearly according to items and serial no. as my screenshot.
If you have a possible solution using dax, it would be greatly appreciated.
Thank you.
OK no problem, but you should state the limitations of sample data you post so that you can get the best answer without wasted time.
Regards
Phil
Hi,
This calculated column formula works
Column = LOOKUPVALUE(Data[Data current],Data[Serial],CALCULATE(MIN(Data[Serial]),FILTER(Data,Data[ITEM]=EARLIER(Data[ITEM])&&Data[Serial]>=EARLIER(Data[Serial])&&Data[Data current]<>BLANK())),Data[ITEM],Data[ITEM])Hope this helps.
