Forum Discussion

rd1920's avatar
rd1920
Frequent Visitor
2 years ago
Solved

Get most recent value, not per status

Hello everyone, 

I want to create a donut chart that shows if a resource has failed to backup or has completed the backup. In order to do this, I also only take the most recent status of that resource. 

 

My data model is like this

 

Resource     End Date                      Status     Last successful backup

A                 19/02/2024           Completed          19/02/2024

B                 19/02/2024           Completed           19/02/2024

B                 18/02/2024           Failed                    17/02/2024

A                 05/01/2024           Completed            ....

A                 02/01/2024           Completed

C                 18/02/2024          Completed

 

I wrote this measure: 

Resource Count by Last Backup = CALCULATE(DISTINCTCOUNT(Backups[Resource]),Backups[End Date]>=Backups[Last Succesfull Backup],Assets[Status] <> "Deprecated")
 
I want to add a pie chart to my report that gives me the most recent status per Resource. So the result should be;
Correct => status = 3 completed ( the most recent value for each resource is completed) 
FALSE=> BUT instead I get = 3 completed & 1 failed (1 failed because it also takes the most recent value for the failed status)
 

 

 
How do I make sure it only takes 1 resource and not the same resource for each status?
 
Thank you so much in advance!
Need Help
Show and Tell
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi rd1920 ,

     

    I used your sample and you can check the results below:

    First I went through the power query and marked Completed as 1 and Failed as 0. Then I implemented it with the following measure.

    Tag = var _t = ADDCOLUMNS('Table',"tag",MAXX(FILTER(ALL('Table'),[Resource]=EARLIER([Resource])&&[End Date]>=EARLIER([End Date])),[Custom]))
RETURN MAXX(_t,ABS([tag]))

FianlStatus = IF([Tag]=1,"Completed","Falied")

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rd1920 ,

     

    I used your sample and you can check the results below:

    First I went through the power query and marked Completed as 1 and Failed as 0. Then I implemented it with the following measure.

    Tag = var _t = ADDCOLUMNS('Table',"tag",MAXX(FILTER(ALL('Table'),[Resource]=EARLIER([Resource])&&[End Date]>=EARLIER([End Date])),[Custom]))
RETURN MAXX(_t,ABS([tag]))

FianlStatus = IF([Tag]=1,"Completed","Falied")

     

    An attachment for your reference. Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.