Need help to Get latest records from table data by adding flag

[VikramAdi]

Need to get latest record based on each file group on below conditions.

PBIX URL : https://drive.google.com/file/d/1YNkpQQKhtOejgPtj48bmS6v6MyadYnfN/view?usp=sharing

1.Sourcesystem="DB"  is latest.

2. if file is not available in Sourcesystem="DB" ,check Sourcesystem="App"  and Sourcesystem="app" and stepdescription = "Import "  is latest.

3. IF stepdescription = "Import "  also file not available. stepdescription = "Transfer "  is latest.

Pls find below screen shot for source data and highlighted color indicates expected output.

Tried below script but expected result not coming . need help.

Test 1 =

IF (

    CALCULATE(

        COUNTROWS(

            VALUES('Data'[FileName])

        ),

        FILTER(

            ALL('Data'),

            'Data'[FileName] = EARLIER('Data'[FileName]) &&

            'Data'[SourceSystem] = "DB"

        )

    ) > 0,

    IF (

        'Data'[SourceSystem] = "DB" ,

        1,

        0

    ),

    IF (

        'Data'[SourceSystem] = "App" &&

        (

            CALCULATE(

                MAX('Data'[StepDescription]),

                FILTER(

                    ALL('Data'),

                    'Data'[FileName] = EARLIER('Data'[FileName]) &&

                    'Data'[SourceSystem] = "App" &&

                    'Data'[StepDescription] = "import"

                )

            ) = 'Data'[StepDescription]

            ||

            CALCULATE(

                MAX('Data'[StepDescription]),

                FILTER(

                    ALL('Data'),

                    'Data'[FileName] = EARLIER('Data'[FileName]) &&

                    'Data'[SourceSystem] = "App" &&

                    'Data'[StepDescription] = "transfer"

                )

            ) = 'Data'[StepDescription]

        ),

        1,

        0

    )

)

above script giving output like below 

.

[lbendlin]

You cannot replace column values in DAX.  This would need to be done in Power Query or in the source system.

[VikramAdi]

@lbendlin Need to add in Power BI side only. atleast is there any way to calculate new status column using current status column. Adding only new status column also fine.

[lbendlin]

Adding a new column would be ok.

New Status = 
var f = [FileName]
var a = filter(ALL(Data),[FileName]=f)
return SWITCH(TRUE(),
[SourceSystem]="APP" && [StepDescription]="import" && countrows(FILTER(a,[SourceSystem]="DB"))=0 && countrows(FILTER(a,[SourceSystem]="APP" && [StepDescription]="import"))=1,"In Progress",
[Status])

[VikramAdi]

Thanks for responce.one of the scenario failed above query. below script working fine. 

ABC 1 =

VAR f = [FileName]

VAR a = FILTER(ALL(Data), [FileName] = f)

RETURN

    SWITCH(

        TRUE(),

        (

            [SourceSystem] = "APP" && [StepDescription] = "import" && Data[Status] = "Success" && COUNTROWS(FILTER(a, [SourceSystem] = "DB")) = 0

            && COUNTROWS(FILTER(a, [SourceSystem] = "APP" && [StepDescription] = "transfer" && Data[Status] = "Success")) > 0

        )

        ||

        (

            [SourceSystem] = "APP" && [StepDescription] = "transfer" && Data[Status] = "Success" && COUNTROWS(FILTER(a, [SourceSystem] = "DB")) = 0

            && COUNTROWS(FILTER(a, [SourceSystem] = "APP" && [StepDescription] = "import" && Data[Status] = "Success")) > 0

        ),

        "In Progress",

        TRUE(),

        Data[Status]

    )

[ThxAlot]

Simple enough, assign different weight to StepDescription,

[lbendlin · Accepted solution]

Flag = 
var f = [FileName]
var a = filter(ALL(Data),[FileName]=f)
return SWITCH(TRUE(),
[SourceSystem]="DB","yellow",
[SourceSystem]="APP" && [StepDescription]="import" && countrows(FILTER(a,[SourceSystem]="DB"))=0 && countrows(FILTER(a,[SourceSystem]="APP" && [StepDescription]="import"))=1,"yellow",
[SourceSystem]="APP" && [StepDescription]="transfer" && countrows(FILTER(a,[SourceSystem]="DB"))=0 && countrows(FILTER(a,[SourceSystem]="APP" && [StepDescription]="import"))=0 && countrows(FILTER(a,[SourceSystem]="APP" && [StepDescription]="transfer"))=1,"yellow"
)

[VikramAdi]

Above Dax script working Good. Thanks for your responce @lbendlin . 

One small change in the requirement. If Sourcesystem="app" and stepdescription = "transfer "  , Sourcesystem="app" and stepdescription = "Import "  in both cases files got "success" file should move to Sourcesystem="DB". but there's no entry to track where file is exactly. for file 1006 and 1007 i should consider latest status as "In progres". how should i achive in dax . pls help me.  

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[VikramAdi]

Sure, Plese find PBIX file : https://drive.google.com/file/d/1aVxHeFh_0T1TpNeMqIzlzAo-dXveYiN0/view?usp=sharing

Source data same as above.reference pls find below screen shot and attched PBIX file .

Out Put :