Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts). My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey.

I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date:

Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]), 
Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY])))  

This works well until you throw Requisition Status into the mix. I have rows for each requisition for every day in 2013. For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date. When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved.
Pivot Table

Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table.

The Requisition Status table looks like this: Requisition Status

Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job. Sometimes it is 1, sometimes is is more than 1. It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.

share|improve this question

migrated from stackoverflow.com Jul 25 '13 at 16:01

This question came from our site for professional and enthusiast programmers.

    
2013-10943 has an entry for 20130601, nothing for the rest of June, then two identical entries for 20130701/02. So it doesn't appear to be a daily snapshot. Is the data or description wrong? If the data is correct and a snapshot is only intermittently recorded, what would you expect to be returned if the filter date were 20130602? –  Mark Storey-Smith Jul 31 '13 at 20:50
    
Yeah...they started collecting the data on a monthly basis, so they only had data on the first of each month until they switched to daily in July. If you filter on 6/2 (for which there is no data) I would expect to get no data. –  mmarie Aug 1 '13 at 7:38

2 Answers 2

up vote 4 down vote accepted

You can try the following calculation:

Openings:=
CALCULATE( SUM( 'Requisition Counts'[NumberofOpeningsQT] )
         , CALCULATETABLE( LASTNONBLANK( 'Requisition Counts'[SnapshotDateKEY] , 1 )
                         , ALL( 'Requisition Status' )
                         )
         )

The important part is in setting the correct filter context for the outer CALCULATE.

With CALCULATETABLE, we define the specific rows which we want the outer CALCULATE to consider: the last non-blank snapshot date for all requisitions (or for a specific requisition, considering the context of each requisition on a pivot table), regardless of any filters in the requisition status, which is achieved using the ALL function.

The measure doesn't work exactly as you want; you say that you would expect the total for Approved to be 2. Using the 2013-11059 requisition from the Power Pivot model as an example, since there is 1 Approved status in 2013-07-01 and another in 2013-07-02, it just considers the last status for the total, resulting in 1.

share|improve this answer
    
Thank you! That worked perfectly. I was apparently not thinking correctly when I said I wanted my final answer to be 2. It should have been 1. This is what I needed. –  mmarie Aug 9 '13 at 17:52
    
One thing I did notice was that I had to replace the semi-colons in the calculation with commas. Just noting for future reference for anyone else. Thanks again, @Gonsalu –  mmarie Aug 9 '13 at 17:55
    
Glad it works for you, @mmarie. I've used semi-colons as parameter separators, because it's the character used in my locale. I'll change it to commas, because that seems to be the character used in Books Online as well. –  Gonsalu Aug 10 '13 at 10:59

I haven't modelled this out yet, but one that comes to mind is to add another FILTER() to your CALCULATE() scope such has this: FILTER('Requisition Status', 'Requisition Status'[Key] = MAX('FactRequisition'[RequisitionKey])). My thought is that adding this will filter the Requisition table to only include the latest Requisition -- I'm assuming that a requisition can't get switched from Appoved to Pending Approval, which would throw a kink into this...

share|improve this answer
    
Thanks for the answer, Josh. That solutions still has the same issue as my previous solution in that for the requisition that switched statuses mid-year, it still shows two rows (one for each status) with the same number for the measure. I believe this is because the status is part of the filter context coming from the pivot table, so the min requisition status key is only summarizing one value and is therefore equal to the filter context. –  mmarie Jul 25 '13 at 15:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.