SSAS OLAP Cube - Sum measure only works when keys are present

2
votes

(This is a mock of my actual setup to help me figure out the problem.)

I have one fact table and one dimension table, linked by an id field.

enter image description here

My goal is to make a measure that sums up all "thing_count" (integer) values in my cube.

If the user splits by nothing, it should show the total "thing_count" for all records in the fact table. If it's split by "category_name" from the dimension, it should show the total "thing_count" for each category.

I tried to achieve this by creating a SUM measure in my cube:

enter image description here

It works, but not in the way I intend it to

It always shows (null) unless I drag in the "id" field from the dimension.


Measure only:

enter image description here

Measure and category:

enter image description here

Measure, category, and id:

enter image description here

How can I make the measure show the value without keys needing to be present?

Edit:

For GregGalloway's request (I've edited the names so the screenshots are easier to follow):

enter image description here enter image description here enter image description here enter image description here

1
ssasolapolap-cubemeasure
Can you go to the DSV and explore the dimension table to view the rows in it? I want to see a screenshot of the row or rows where id=1 in your dimension. And can you screenshot the Dimension Usage tab in the cube editor? And can you screenshot the Attribute Relationships tab of the dimension editor? – GregGalloway
You can skip the above request if my answer fixes the problem, obviously. – GregGalloway
Screenshots added @GregGalloway – Lil' Bits

1 Answers

2
votes

One common explanation for this behavior (no aggregation) is that you have inadvertently commented out the CALCULATE; statement in your MDX script in the cube. Please check that statement is still present.