topic Re: DAX query HELP (it is lookup/firstnonblank/measure??) in DAX Commands and Tips

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

sanchar123 — Fri, 18 Dec 2020 20:30:44 GMT

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.

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

Anonymous — Fri, 18 Dec 2020 23:56:58 GMT

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,

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

amitchandak — Sat, 19 Dec 2020 03:55:23 GMT

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)

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

sanchar123 — Mon, 21 Dec 2020 16:02:41 GMT

amitchandak Awesome solution!! thanks a ton!