Odtnhj

I need to create a distinct count of people who fall into two different dimensions.

One is called [Student Research Degree].[Is Research Degree Current].&[Yes]
The other is called [Student Research Degree].[Is Research Degree Complete].&[Yes]

If one or the other are Yes, or both, then I need to count the record.
If both are no, I can exclude it. I have a row counter measure called [Measures].[Student ID Distinct Count Hidden] already in place.

If I use just one element with the measure, I get the right answer, but if I try to cross join the other elements, I get a result of NULL.

eg

AGGREGATE(CROSSJOIN(
 [Student Research Degree].[Is Research Degree Current].&[Yes] 
 ,[Student Research Degree].[Is Research Degree Complete].&[Yes]
), [Measures].[Student ID Distinct Count Hidden])

I am aware that I can just land an extra value in the ETL, and have SQL do the work, and in the end this might be the solution. Is there a way of doing an OR statement on this sort of thing?

No, the TUPLE of &[YES], &[YES] doesn't create an OR situation, where I want [NO]s when the other is yes.

I started looking at a subtractive approach where I started with the ALL set, and removed the distinct count of invalid combinations in a tuple and subtracted that from the grand total. This approach did work, but ONLY because the data allowed for it. If a person could have been in multiple combinations, this wouldn't have worked.

I'm currently testing that approach with the rest of the cube. By all appearances this works perfectly, but I will go with ETL if any bugs or mismatches can be proven.