Calculeted column to mark specific entries

[Anonymous]

Hi there, 

I am new with Power BI and DAX and this is my first post as hope you can help with this issue.

I have a history table (bi_avkhis) with different IDs (AVKID)and statuses (AKVSTATID) (see the table below). 

After AVKSTATID = 100 or 110 or 120 or 130 shouldn't be registered entries anymore so I need to sort out these entries if they are registered.

I want to create a column to mark with 1 the entries after status ID = 100 / 110 / 120 / 130 by AVKID.

Can you please help on this? Thank you in advance.

[Anonymous]

@Jihwan_Kim Great! It works, thank you! 

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and that attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Flag CC =
VAR _target = { 100, 11, 120, 130 }
VAR _indexnumberhitstarget =
    MAXX (
        FILTER (
            BI_AVKHIS,
            BI_AVKHIS[AVKSTATID]
                IN _target
                    && BI_AVKHIS[AVKID] = EARLIER ( BI_AVKHIS[AVKID] )
        ),
        BI_AVKHIS[Index]
    )
VAR _indexnumberlistaftertarget =
    SUMMARIZE (
        FILTER (
            BI_AVKHIS,
            BI_AVKHIS[AVKID] = EARLIER ( BI_AVKHIS[AVKID] )
                && BI_AVKHIS[Index] > _indexnumberhitstarget
        ),
        BI_AVKHIS[Index]
    )
RETURN
    IF ( BI_AVKHIS[Index] IN _indexnumberlistaftertarget, 1 )