Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape 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.
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.
Solved! Go to Solution.
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"
)
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.
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.
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 .
Out Put :
User | Count |
---|---|
25 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
40 | |
28 | |
28 | |
22 | |
21 |