Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
My date tables was just this
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
My date tables was just this
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
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. 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |