MDX distinctcount conditional calculation

Question

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 ?

Answer 1

The best way to deal with this would be to model this correctly:

Define a "Responsible" dimension containing one entry for each person appearing either in the Responsible or in the Responsible2 column, and a "Document" dimension table containing one row per distinct document. Each of these dimension tables should also have a numeric primary key column, and may have additional columns for other properties of the person (maybe department, gender, ...) or document (maybe name, path in file system, creation date, number of pages, ...).

Then you would have a fact table which has three columns: two foreign key columns referencing the primary keys of the dimension tables, and a measure column, maybe called DocCount, which would be 1 / number of authors referenced by the document id (i. e. 1 for one author, and 0.5 for two authors).

Then all you would have to do is to set up the cube based on this. You would not need any complex MDX calculations, everything would be handled by Analysis Services based on the cube structure. This would also allow to use any number of responsible persons and not just one or two for a document.

It would be possible to solve your request in MDX with your design, but that would require some slightly complex MDX.

Answer 2

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.