SSAS Tabular Date Filter Across Role Playing Date Dimensions

Question

Using an accumulating snapshot fact, I multiple role-playing date dimensions in my Tabular Cube.

Users would like to be able to see when ANY of the date events occurred during a given period (as opposed to ALL of the date events which is quite natural in the tool).

This is essentially an OR statement.

I have tried adding another instance of the date dimension and then joining all of the role-playing dimensions to it (shown below), but am not having much success.

Not fully shown, but indicated are two fact tables related to the dimensions as well.

How can I essentially apply an OR condition to multiple dimensions from a pivot table?

The problem at hand is to retrieve the number of orders in a given month that are Received, Returned, or Invoiced. As in:

Time Period = January 2016
Received Count = 20
Returned Count = 16
Invoiced Count = 32

Thus, a fact record with ReceivedDateSID = 20160101 and ReturnedDateSID =20160115 and InvoicedDateSID = 20160130 should count once in each measure.

Answer 1

One straightforward approach that will perform great but require 3x more memory is to:

1. Have one DimDate
2. Have FactReceipt, FactReturn, FactInvoice. FactReceipt joins to DimDate on ReceiptDateKey. FactReturn joins to DimDate on ReturnedDateKey. FactInvoice joins to DimDate on InvoiceDateKey.
3. You can even put a where clause on the SQL view defining those fact tables. For example you only need the 1% of orders which are returned in FactReturn.

I personally prefer this approach to tons of complex DAX. Anytime a measure (like Returned Count) only makes sense with one role-playing date dimension I consider this approach.

You may also consider hiding all but one of those fact tables and putting all the calculated measures inside the one main fact table. That may reduce confusion for your users. Though drillthrough wouldn't work right then.