DAX query HELP (it is lookup/firstnonblank/measure??)

[sanchar123]

hi team.

loadNum(incremental)	notification(1 or 2 or 3)	Desired Column
3	0	0
3	0	0
3	0	0
3	0	0
3	0	0
3	2	99
3	0	99
3	0	99
3	13	99
3	0	99
3	0	99
3	16	99
3	0	99
3	0	99
3	0	99
4	0	0
4	0	0
4	0	0
4	1	99
4	0	99
4	0	99
4	0	99
4	0	99
4	16	99
4	0	99
5	0	0
5	0	0
5	0	0
5	3	99
5	0	99
5	0	99
5	0	99

LoadNumber is incremental and i want only first few values till I hit first value (1or2or3)from column 2. rest all can be any value.

Please let me know how can it be done.

Thanks in advance.

[sanchar123]

@amitchandak  Awesome solution!! thanks a ton!

[amitchandak · Accepted solution]

@sanchar123 ,

Add a index column https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

then try a new column

new column =
var _1 = minx(filter(Table,[loadNum(incremental)] = earlier([loadNum(incremental)]) && [notification(1 or 2 or 3)] =0),[Index])
var _2 = [loadNum(incremental)]
return
if([loadNum(incremental)] =_2 && [Index] >= _1, 99,0)

[Anonymous]

Hi,

If I understood your issue correctly then my suggestion will be to do as follows -

• Clean up your table to remove duplicates.
• Use FIRSTNONBLANK to get the incremental loadnum.

Best Regards,

PG