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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Sander_NL
Helper I
Helper I

Need help multiple rows of data into one unique table and calculation last 30 days

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

3 ACCEPTED SOLUTIONS

@Sander_NL

 

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)

Regards
Zubair

Please try my custom visuals

View solution in original post

@Zubair_Muhammad Thank you very much for your quick response. This solution is everything I need. 

View solution in original post

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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 )

Regards
Zubair

Please try my custom visuals

@Sander_NL

 

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)

Regards
Zubair

Please try my custom visuals

@Zubair_Muhammad Thank you very much for your quick response. This solution is everything I need. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.