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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors