topic Re: Need help to Get latest records from table data by adding flag in DAX Commands and Tips

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

VikramAdi — Sun, 28 Jan 2024 18:25:21 GMT

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

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]

)

above script giving output like below

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

lbendlin — Sun, 28 Jan 2024 20:59:41 GMT

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

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

ThxAlot — Mon, 29 Jan 2024 00:34:23 GMT

Simple enough, assign different weight to StepDescription,

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

VikramAdi — Mon, 29 Jan 2024 16:12:01 GMT

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.

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

lbendlin — Mon, 29 Jan 2024 16:10:24 GMT

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

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

VikramAdi — Mon, 29 Jan 2024 16:56:18 GMT

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 :

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

lbendlin — Mon, 29 Jan 2024 17:00:09 GMT

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

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

VikramAdi — Mon, 29 Jan 2024 17:14:45 GMT

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.

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

lbendlin — Mon, 29 Jan 2024 17:50:30 GMT

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

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

VikramAdi — Mon, 29 Jan 2024 18:03:29 GMT

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]

)