DAX for Loop and write mechanism in a calculated column

[Anonymous]

Hello , 

I have some sample data shown as below.

I've been struggling to write DAX for the column Flag_2 which is sort of a cursor / loop that iterates over Month EndDate , for every unique ID. The objective of Flag_2 is to meet the first occurence of "1" in the column Flag_1 whilst looping ascending over Month Enddate, and marking the rest as 'x'. or -1, whatever works. 

I'm pretty sure with collecting the date ranges, and writing calculate aggregations on them but i'm lost with not having the ability to implement loops and local variables or flags. How does one solve this problem in DAX ?

[Anonymous]

HI @Anonymous,

It seems like you check the first flag1 based on id and month name fields.

I'd like to suggest you add an index column in the query editor based on these two fields, then you can loop records based on the current id and index to remark the first '1' on flag_2 filed.

Regards,

Moonlight

[Anonymous]

Hello , 
Thank you for that suggestion ! but i have flag_1 as a calculated column in my model that's actually a result of many other logical operations, conditions. I also do this on different levels, an ID , month_name , person name fields ... etc. 

But feel free to share how you would loop records after making an index column/concatenated column and flag the first occurence as 1, and the rest as 'x'. 

[Anonymous · Accepted solution]

Hi @Anonymous,

The formula expressions logic:

1. Get the first date based on 'flag_1' field value and id.

2. Use the 'first date' to compare with current date and check 'flag_1' field value to remark tags.

formula =
VAR currDate =
    DATEVALUE ( "1-" & Table[Month Name] )
VAR _firstDate =
    CALCULATE (
        MIN ( Table[Month Name] ),
        FILTER (
            Table,
            [ID] = EARLIER ( Table[ID] )
                && [Flag_1] = 1
                && DATEVALUE ( "1-" & Table[Month Name] )
                    < DATEVALUE ( "1-" & EARLIER ( Table[Month Name] ) )
        )
    )
RETURN
    IF (
        Table[Flag_1] = 1
            && currDate
                = DATEVALUE ( "1-" & _firstDate ),
        "1",
        IF ( currDate > DATEVALUE ( "1-" & _firstDate ), "x", "0" )
    )

Regards,

Moonlight

[Anonymous]

Thank you for this , 
I've used some workarounds with summations, distinct counts and such logics, but i'm going to experiment more with your solution. 🙂 

[v-shex-msft]

Hi @Anonymous,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[amitchandak]

@Anonymous ,

a new column =

var _date = minx(filter(Table, [id] =earlier([ID]) ), [Date])

return

Switch(True(),

[Date] < _date, "0",

[Date] =  _date, "1",

"x"

)

[Anonymous]

I do not see the new column's DAX using the column Flag_1 at all. 

This answer doesn't work because it dummifies the problem- bearing the first month's ( min month ) value to be 1 and the rest turn 'x'. It doesnt consider Flag_1.