Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Shape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.

Reply
VikramAdi
Helper II
Helper II

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.

 

VikramAdi_0-1706465034308.png

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 
.VikramAdi_0-1706466169183.png

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

lbendlin_0-1706475566716.png

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

 

View solution in original post

9 REPLIES 9
lbendlin
Super User
Super User

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

@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.

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

 

 

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
Super User
Super User

Simple enough, assign different weight to StepDescription,

ThxAlot_1-1706488538446.png

 

ThxAlot_0-1706488491356.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



lbendlin
Super User
Super User

lbendlin_0-1706475566716.png

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

 

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.  

VikramAdi_0-1706544265940.png

 

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

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 .

VikramAdi_1-1706545526772.png

 

Out Put : 

VikramAdi_0-1706545387504.png

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.