Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have 2 tables:
1) List of servers that should be backed up (CMDB table)
2) I have a dataset from an API call. This contains multiple lines of data to determine whether a backup has been successful.
Now the server name is unique and I want to know what for each server:
1) The status of the last backup is
2) The date of the last successful backup.
3) How many times the backup has been successful in the last 30 days.
Not every server has been backed up (e.g. new servers) in the last 30 days.
If a server does appear in the table CMDB but does not have a backup status (missed/completed etc) then that status should not be found.
Can someone give me a direction on how to do this?
Example of data
Solved! Go to Solution.
Last Completed_Backup = CALCULATE ( MAX ( Blad1[Backup schedule] ), Blad1[Status] = "Completed" )
Number of completed backups = VAR result=CALCULATE(COUNTROWS(Blad1),Blad1[Status]="Completed") RETURN IF(ISBLANK(result),0,result)
@Zubair_Muhammad Thank you very much for your quick response. This solution is everything I need.
HI @Sander_NL
First create a relationship between your tables using the common Field i.e. Servers Field
Now you can use these calculated columns
Date last backup = CALCULATE ( MAX ( Blad1[Backup schedule] ) )
Status Last Backup = VAR Result = CALCULATE ( FIRSTNONBLANK ( Blad1[Status], 1 ), LASTDATE ( Blad1[Backup schedule] ) ) RETURN IF ( ISBLANK ( Result ), "Not Found", Result )
Last Completed_Backup = CALCULATE ( MAX ( Blad1[Backup schedule] ), Blad1[Status] = "Completed" )
Number of completed backups = VAR result=CALCULATE(COUNTROWS(Blad1),Blad1[Status]="Completed") RETURN IF(ISBLANK(result),0,result)
@Zubair_Muhammad Thank you very much for your quick response. This solution is everything I need.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |