Sunday, February 17, 2013 1:58 AM
I have a list of applications that I have deployed and they have been versioned many times. Data is sometimes incomplete like missing version numbers and the like. Date they were deployed is consistantly filled in. Below is my table layout:
And here is some sample data from table_owssvr. Assuming the country table is not relevant here directly unless someone wants sample data from there and DimDate is a time dimension:
So here's the deal. I am trying to show for each quarter, how many applications launched. Kind of an increasing graph as more and more apps get deployed in my company. I don't care about versions. Only care when the application first launched. As you can see on the apps, App1 for example, launched in Russia and only care about the 7/31/2012 version in 2012Q3 for the report. Anything that has a blank launch date should be ignored. Anything with only one entry and date use as is. Now here's the funny part. See App9? Shows across multiple countries? They aren't the same app. They are named the same but each country came out with their own app. So I guess you can say a combination of Appplication and Country make a record unique. Any clue how I get a graph that sums up each quarter of the first time an app was launched for a given country and keep summing each quarter? I thought I got kind of close but think the App9 part will throw me if I really look at the data and it seems to be returning more results than I thought it would. This is the measure I tried to use:=sumx(values(Table_owssvr[Application]), if((calculate(min(Table_owssvr[Launch Date])) <= max(DimDate[Date]) && (calculate(min(Table_owssvr[Launch Date])) <> BLANK())), 1,BLANK()))
Problem is I got weird results if the app was launched across multiple quarters it would show both quarters. If it launched 5 times in one quarter it would seem to return once and work but obviously as shows below that isn't the case. Below I am typing in as it only allows two pictures:
Application Quarter-Year Country Measure2
App1 2012Q3 Russia 1
App1 2013Q4 Russua 1
Any help would be appreciated.
- Edited by syarbrou1 Sunday, February 17, 2013 2:33 AM
Monday, February 18, 2013 3:37 PM
Here's a solution that uses calculated columns to simplify things a bit.
In your table of launches, calculate an initial launch date for each app / country combination:
=CALCULATE(MIN(Launch[LaunchDate]) ,ALLEXCEPT(Launch,Launch[Application],Launch[Country]) )
And a flag for initial launch that will make your filtered counts much easier:
=IF([InitialLaunchDate]=[LaunchDate] && NOT(ISBLANK([LaunchDate])),TRUE(),FALSE())
Then you can create a relationship to your date table on the initial launch date created above. I would expect this to be the second relationship to the date table (first one being on regular launch date), so the second relationship would be inactive.
Now, with those in place, create the following calculated measures:
CountLaunch:=COUNTROWS(Launch) CountInitialLaunch:=CALCULATE([CountLaunch] ,Launch[InitialLaunchFlag] = TRUE() ,USERELATIONSHIP(Launch[InitialLaunchDate], 'Date'[LaunchDate]) )
That first measure is just a base measure to simplify the second calc. The second measure uses filters to first filter out non-initial launch rows, and then activate the relationship to the date table on the initialLaunchDate.
Now, testing this out in a pivot, you can put YearQuarter on the rows axis and drop both measures in the Values area. I built a little prototype with some of your Launch table rows, and was able to get this result:
Here's a link to the file on my SkyDrive for reference. Let me know if that helps.
Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
- Edited by Brent Greenwood Monday, February 18, 2013 3:38 PM
- Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 6:21 AM
- Unmarked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 6:21 AM
- Proposed As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Thursday, February 21, 2013 6:21 AM
- Marked As Answer by Elvis LongMicrosoft Contingent Staff, Moderator Monday, February 25, 2013 3:32 AM