SSAS cube fact and dimension relationship

I have a cube that all the tables has one row per ID and have measure in all of them except for the calendar dimension.

When I view the measures in excel from Table 1 with attributes from table 2, I have no issues, but when I view the measures from Table 2 with attributes from Table 1 it returns the same number for each value of the attributes:

BillingCycle        HCD Accounts For Review
7                     4067
14                    4067
30                    4067

How can I overcome this issue? Does this have anything to do with the direction of the relationship between the tables?

What you are seeing is a typical example of a missing dimension relationship.

Yes it has something to do with the relations in your dsv but easy to solve with dimension relationships.

If you add a dimension to a measure group/cube/fact table the dimension relations are automatically generated based on the relations in the data source view, if a relation isn't present a dimension relation has to be created manually.

So in your example you need to go to the cube where the measure group is residing and select dimension relationships and define the relation between your dimension key and the fact table.

See defining dimension relationships for details on how to do exactly that.

It's actually fairly obvious why SSAS would behave like that. If there is no relation between axis and figures, there is no way a slicer can have impact on the figures.

If you are using a Tabular model, unless it lets you add relations in both directions I fear you'll be out of luck and need to design your data warehouse better using actual fact tables and dimension tables.

StackExchange Database Administrators Q#123678, answer score: 3