I'm really new in MDX and having trouble calculating results. I'm working with SSAS 2012 and designing my cube in Visual Studio. This is my current query:
WITH MEMBER [DocCount] AS
IIF(([Measures].[Responsible Count] > 0 AND [Measures].[Responsible2 Count] > 0),
[Measures].[DocName]*0.5, [Measures].[DocName])
SELECT {[DocCount]} ON 0, [Analysis Table].[Responsible].MEMBERS ON 1
FROM [database]
SQL table is this
ID DocName Responsible Responsible2
1 100001 John Doe Mary Jane
2 100001 Mary Jane John Doe
3 100002 Mike Doe NULL
4 100003 John Doe Mike Doe
5 100003 John Doe Mike Doe
6 100004 Mary Jane NULL
[Measures].[Responsible Count] is "count of non-empty values" selection in VS
[Measures].[Responsible2 Count] is "count of non-empty values" selection in VS
[Measures].[DocName] is "distinct count" selection in VS
I need to calculate DISTINCTCOUNT DocName, but when Responsible AND Responsible2 is not null, Count should be *0.5. The problem is that Cube data is aggregated and only then my [DocCount] IIF is evaluated. Current results are like this:
Responsible DocCount
John Doe 1 --(2*0.5) because it distinct counts DocName and then *0.5 it
Mary Jane 1 -- (2*0.5) it does not care that ID 6 responsible2 is null
Mike Doe 1 -- (1) this is correct
I want this Final result:
Responsible DocCount
John Doe 1 --(0.5+0.5) ID 1,2 and 4,5
Mary Jane 1.5 --(0.5 + 1) ID 1,2 and 6
Mike Doe 1 --(1 ) ID 3
how to modify my query for it to calculate correct results ?
Finally I made solution myself. I replaced my fact table with named query where I added additional column which will find only distinct rows by DocName and evaluate if that row has both Responsible and Responsible2 not null. If yes - it will enter 0.5, else - 1. Then it was plain simple to add Measure out of this column and test my results.