How to find and mark specific rows in conditional window function by DAX?

[TungNguyen_19]

Hello everyone,

I have been stuck with this problem and I really need your help. Below I have this demo partition of my master table:

Now I have to mark 1 for all rows between VAN and LDFULL code by DAX, the rest will be marked 0. The result look like this:

I have try to combine functions  OFFSET with PARTITIONBY but all I just can do is finding next move base on Serial.

Please help me, many thanks to you guys

[Ahmedx]

watch my video i did what you want with power query
VIDEO :

https://1drv.ms/v/s!AiUZ0Ws7G26RiU40AmKekZWu3wvj?e=cA5H3K
Sample PBIX file attached

https://1drv.ms/u/s!AiUZ0Ws7G26RiU0JufLFWKnJVFBB?e=bUD7sg

[TungNguyen_19]

Hi @Ahmedx 

Thanks for your help, but do you know how to do this work by using DAX? Because the data is from database and I can not have credential for editing on Power Query.

[Ahmedx]

if we do it in DAX, then we still need the index column. it won't work without this column

[TungNguyen_19]

Hi, I don't know what is the role of index column. Also the data have other column is Event Date which act same as serial column but I can set index base on this one for group by ID.
Can you please show how you can do this by DAX in your scenerio? 

[Ahmedx · Accepted solution]

pls try this

specific rows 2.pbix

[TungNguyen_19]

Hi  
Wonderful, your function is really great. But can I ask you what does this row doing?

_Indx = MAXX(FILTER('Table', [Index] <= _t1 && NOT ISBLANK([falg])), [Index])

I mean when the function come to this row how can it know to fill down the number 4 to this cell because the flag cell this row is blank and the condition in FILTER function is not blank?  

Many thanks ❤️

@Ahmedx