April 14, 2008 at 1:51 am
Problem: I have some documents that can pass through some status changes. Each document is related to a product. I need to calculate the number of documents that were in “submitted” status only once. E.g. if document with id = 6 was submitted once in January and again in March, it should be counted as submitted only once for January and March but not for Q1.
So, I defined a fact table that contains the status changes to “submitted”, F_DocSubmissions, and a dimension containing the documents, Docs. In Docs I defined a Products hierarchy; I defined a helper measure, [submitted document count] which counts the submissions, and a calculated member with the following formula:
Sum(Descendants([Docs].[Products].CurrentMember, [Docs].[Products].[Doc ID]),
Iif(1=[submitted document count], 1, 0))
This member aggregates as expected on time dimension, but here comes the problem: on Products hierarchy, it always displays the total for all documents related to a product; even if I filter out some documents from a product… the total is still calculated for all documents.I’m a new to OLAP stuff so I think that maybe I’m missing something obvious?
Thank you for any advice.
April 14, 2008 at 3:52 am
I'll think you need as distint count like
DISTINCTCOUNT( [Sale[Docs].[Products].[Doc ID].Members )
I'm not sure if you need members or Currentmenbers at the end of this Statement.
W. Lengenfelder
April 15, 2008 at 5:30 am
Thanks for answer. Unfortunately it did not help, so I used another approach.
I added a new measure myMeasure with sum as aggregation on a column, then override leaves using the following calculation:
SCOPE ([Measures].[myMeasure], [Time].[Time].Members, [Docs].[Products].[Doc ID].Members);
this = Iif(1=[submitted document count], 1, null);
END SCOPE;
This works correctly.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply