filipetatarli (3/25/2013)
I'm studying MDX and can't solve one problem.To simplify there is a fact table (fact_Proc) and two dimension (Dim1 and Dim2). The structure is:
fact_Proc :
id int identity(1,1)
id_Dim1 int
id_Dim2 int
Dim1 :
id_Dim 1 int identity(1,1)
Dim2:
id_Dim 2 int identity(1,1)
And then I filled with some data the fact table:
ID ID_DIM1 ID_DIM2
1 0 1
2 1 2
3 2 3
4 0 4
What i want is count the distinct register of the ID_DIM1 column without consider the 0 value in fact table.
How i do it using the calculated member ? Do i need create a measure first ?
Thanks in advance
I don't really understand this structure, I realize you've simplified for the question but perhaps too much. Your fact_proc has an ID identity key that is sort of a surrogate key but it doesn't link to any other table. The only other two fields are keys that link to the two dimensions. What is the measure (fact) in the fact table? If the fact table only consists of keys there is not much you can do with it.
If you had an "actual measure" in that table then you could do a count on it, and exclude dimension references where the ID_DIM1 is 0.
Assuming you'd defined [fact_proc].[actual measure count] in the cube,
Perhaps like this
Select [fact_proc].[actual measure count] ON COLUMNS,
{} ON ROWS
FROM
(Select
( - {[Dim1].[IDDIm1].[IDDIm1]&[0]} ) ON COLUMNS
FROM [NAME OF CUBE]
)