Multiple Joins/Multiple Aggregations in Microsoft SSAS Tabular Model.

In Microsoft SSAS Tabular Model you can make multiple joins between fact and dimensions. The first join is shown as solid line. Any additional joins are shown as dash lines.

However in SQL Server 2012 Tabular Model is using only the first join for value aggregations. It is OK when the model in a simple star schema. However it will not be enough in conformed dimension.

For example: A fact order table has date_submitted and date_closed. both values joined to a Time Dimension table.
The goal is to aggregate data to report weekly, monthly aggregation based on date_submitted. But when attempt to count data based on date_closed the result are incorrect due to the tabular mode is using the first join only, but not the second join.

The solution is that in SSAS Tabular Model you can specify a aggregated value to use the second join. use the USERALTIONSHIP() expression to specify the join after the CALCULATE value.

Count of Date Closed:=CALCULATE(COUNTA('FactTableName'[ColumnName]),USERELATIONSHIP('FactTableName'[ColumnName],'DimensionTableName'[PK_Dim]))