Forum Discussion
SUM of Distinct Rows
I'm new to PowerBI and I can't seem to wrap my head around this calculation. My end goal is to look at the average duration of SQL Job executions over the past 7 days and compare this value to the most recent execution, but the underlying structure of the data makes analysis pretty hard.
I'm not sure if I have to break down my data even further, or if this is just going to be one large DAX statement. So here goes...
I currently have a table with 8 columns. These columns are the SQL Job Name, Step ID, Run Duration, Run Date, Current Date, Past Week, Is Past Week, Is Today.
SQL Job Name has a one-to-many relationship with SQL Job Step (there are multiple steps in a job).
So I need to first calculate the sum of duration for all steps under a job on a given day, then average that value for the past 7 days. I don't know where to start on this in DAX, and I'm not sure (it may be obvious given the format of my table) if I should do this calculation as additional columns (data inflation?) or create a new table. I've gone through the DAX training provided on the Microsoft website, but couldn't find any functions that seemed to assist in this specific application.
Should be something along the lines of:
Measure = CALCULATE(AVERAGE([Run Duration]),FILTER(Table,[Run Date]>TODAY()-7))
8 Replies
Should be something along the lines of:
Measure = CALCULATE(AVERAGE([Run Duration]),FILTER(Table,[Run Date]>TODAY()-7))
At first glance, this doesn't appear to address the one-to-many relationship between the SQL Job and underlying steps.
It's a good first step, I'll see if I can use this.
You would put SQL Job and this Measure in a table. If you have the relationship built between them, then the context should be correct and you will get the right answer.
