3

(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

Lil' Bits
  • 898
  • 2
  • 9
  • 24
  • 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 Mar 03 '19 at 07:16
  • You can skip the above request if my answer fixes the problem, obviously. – GregGalloway Mar 03 '19 at 07:54
  • Screenshots added @GregGalloway – Lil' Bits Mar 04 '19 at 16:42

1 Answers1

2

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.

GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • I'm not using my own MDX script to generate the measure. I'm using the built-in method through SSAS per the screenshots in my question. – Lil' Bits Mar 04 '19 at 16:30
  • Can you go to the Calculations tab of the cube designer and switch to the Script View and screenshot it? I want to be 100% sure the CALCULATE statement is still there. – GregGalloway Mar 04 '19 at 17:52
  • See screenshot in original post. There aren't any calculated members. Form view shows "There are currently no calculations defined for this model." – Lil' Bits Mar 04 '19 at 18:50
  • So you need to add the following to the MDX script and deploy. That should fix it: `CALCULATE;` – GregGalloway Mar 04 '19 at 19:00
  • I **CAN'T** believe that's what was causing this issue. I saw that exact answer on other posts, but in context of people creating their own MDX measures. I didn't know you needed that statement there even if you're just using the in-built measure generator. Your bounty will be delivered to you tomorrow Greg! – Lil' Bits Mar 04 '19 at 19:13
  • @Lil'Bits i think this is the shortest bounty ever. :) In order to get more upvotes for the question and answer, don't offer the bounty until its time ends – Hadi Mar 04 '19 at 19:40
  • 1
    It was a very well asked question. You have my upvote. Glad we could find a quick solution. Cheers – GregGalloway Mar 04 '19 at 19:50