Forum Discussion

VikramAdi's avatar
VikramAdi
Icon for Helper II rankHelper II
2 years ago
Solved

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

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 
.

 

@Dax
@dax @help
@Dax help
@get help
@Power BI
  • lbendlin's avatar
    lbendlin
    2 years ago

    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"
)

     

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      VikramAdi
      Icon for Helper II rankHelper II
      2 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

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

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

    • VikramAdi's avatar
      VikramAdi
      Icon for Helper II rankHelper II
      2 years ago

      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's avatar
        lbendlin
        Icon for Super User rankSuper User
        2 years ago

        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])