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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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