I have a SSAS cube with JOBS and PERSON as dimensions and a factless fact table COUNT_CUBE. 2 measures have been added to the cube using wizard as follows-
measure name=[ErrorCount Jobs] ;Usage= count of non-empty values ;Source table =JOBS; soiurce coulumn=Error Id

measure name=[ErrorCount Person] ;Usage= count of non-empty values ;Source table =PERSON; soiurce column=Error Id

Also each dimnsion PERSON and JOB has below attributes:-
[data rule name] which specifies the data rule on the dimension.
[Error Id] specifies the error id generated whenever the data rule fails .The same data rule can fail multiple times for a single employee or multiple employees.
[Empluyee Id]-The emolyee id for which the data rules are failing

I needed to join 2 dimesions and find the number of times a particular dat rule is failing

I have tried the below query:-


WITH MEMBER
[Measures].[Name Of failed Data rule] AS (
IIf
([Jobs].[Data Rule Name].currentmember IS [Jobs].[Data Rule Name].defaultmember,
"Person: " + [Person].[Data Rule Name].currentmember.name,
"Jobs: " + [Jobs].[Data Rule Name].currentmember.name
))

MEMBER [Measures].[Error Frequency] AS (
IIf
([Jobs].[Data Rule Name].currentmember IS [Jobs].[Data Rule Name].defaultmember,

[Measures].[ErrorCount Jobs]

,[Measures].[ErrorCount Person]

))
SELECT
{[Measures].[Name Of failed Data rule] ,[Measures].[Error Frequency] } on 0,

{

{

nonempty((except([Jobs].[Data Rule Name].members ,{[Jobs].[Data Rule Name].[all],[Jobs].[Data Rule Name].&}) * [Person].[Data Rule Name].defaultmember))},

{


nonempty([Jobs].[Data Rule Name].defaultmember * (Except([Person].[Data Rule Name].members,{[Person].[Data Rule Name].[all],[Person].[Data Rule Name].&})))}

}


ON 1

FROM [pp audit]

output

Was able to get it working partialy as in terms of my desired format...but the no of times a data rule fails ie [Measures].[Error Frequency] in a dimesion is shown incorrectly as sum of all datarules in that particular dimension and that too interchanges with other dimension.
Actual output:
Name Of failed Data rule Error Freq

Jobs:datarule1 3
Person:datarule2 200
Person:datarule3 200


200 actually is the error freq of Jobs:datarule1 and is the only datraule in JOBS dimnsion
3 is total error freq in PERSON dimnsion ,datarule2 fails 2 times and datarule3 fails 1 time.So 2+1 =3 times.
Result I belivw due to the cross hoin concept interchanges the values .

Expected output:
Name Of failed Data rule Error Freq

Jobs:datarule1 200
Personatrule 2 2
Personatrule 3 1


Plz help .

Thanks