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
chriswal
New Member

create table to include missing dates

Hi folks,

 

Been trying to figure this one out got close but could not get it work as a needed.

 

Have log file from a backup service that includes records when the backup for different servers was successful but no record when a failure occurs. I would like to produce some statistics on success vs failures and therefore would like a table that includes all dates and if the backup was successful or not. Using a Measure I could get a Matrix to display the correct information but I could not get a  Card and Graph visual to calcuate correctly.

 

So I have a date dimension table and the log table, log table is (for the first few dates of May)

date,server,volume

5/1/2017,svr1,100

5/3/2017,svr1,120

5/1/2017,svr2,200

5/4/2017,svr2,300

 

Date Dimension table has (for  few years)

date

5/1/2017

5/2/2017

5/3/2017

5/4/2017

5/5/2017

5/6/2017

 

 

How do I create a table that would have

date,server,volume,result

5/1/2017,svr1,100,success

5/2/2017,svr1,,fail

5/3/2017,svr1,100,success

5/4/2017,svr1,,fail

5/5/2017,svr1,,fail

5/6/2017,svr1,,fail

5/1/2017,svr2,200,success

5/2/2017,svr2,,fail

5/3/2017,svr2,,fail

5/4/2017,svr2,300,success

5/5/2017,svr2,,fail

5/6/2017,svr2,,fail

 

 

for all the dates in date dimension table?

 

Thanks for any help.

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

Hi @chriswal

 

I had some success with the following calculated table 

 

 

New Table = ADDCOLUMNS
            (NATURALLEFTOUTERJOIN(
                SELECTCOLUMNS(
                        CROSSJOIN(SUMMARIZECOLUMNS('log'[Server]),'date') ,
                        "Server" , 'log'[Server] ,
                        "Date" , DATE(YEAR('date'[Date]),MONTH('date'[Date]),DAY('date'[Date]))
                        )
                ,
                SELECTCOLUMNS(
			'log',
			"Server",[Server],					
			"Date", DATE(YEAR([Date]),MONTH([Date]),DAY([Date])),
                        "Volumne",[volume]
			)                    
                    ),
             "Result",
             IF(
                 ISBLANK([Volumne]),
                 "Fail",
                 "Success")
                )

 

which produced this

 

nloj.png

 

My date tables was just this

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

Hi @chriswal

 

I had some success with the following calculated table 

 

 

New Table = ADDCOLUMNS
            (NATURALLEFTOUTERJOIN(
                SELECTCOLUMNS(
                        CROSSJOIN(SUMMARIZECOLUMNS('log'[Server]),'date') ,
                        "Server" , 'log'[Server] ,
                        "Date" , DATE(YEAR('date'[Date]),MONTH('date'[Date]),DAY('date'[Date]))
                        )
                ,
                SELECTCOLUMNS(
			'log',
			"Server",[Server],					
			"Date", DATE(YEAR([Date]),MONTH([Date]),DAY([Date])),
                        "Volumne",[volume]
			)                    
                    ),
             "Result",
             IF(
                 ISBLANK([Volumne]),
                 "Fail",
                 "Success")
                )

 

which produced this

 

nloj.png

 

My date tables was just this

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Thanks Phil,

 

Pointed me in the right direction but decided to do it in the Query Editor instead of DAX. Was able to do more transformations on the result that way.

 

 

 

Cheers

Hi @chriswal @Phil_Seamark

 

Can you Please let me know  ,how you got it through query editor. we also have the same situation.

In my  case i need to calculate ratio where denominator value comes from 1st table and numerator value comes from Log table

,if there is no value in log i am mising those dates , how to achive those missing dates so  that i will 0/denomintor = 0 %

Nice work.  I'd say using the Query Editor is the right approach.  

 

I just enjoyed finding a use for the NATURALLEFTOUTERJOIN function.  🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.