Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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. 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
89 | |
81 | |
65 | |
65 | |
61 |
User | Count |
---|---|
170 | |
115 | |
100 | |
73 | |
67 |