Using Multiple Date Dimensions in One Measure

[Anonymous]

Hi Everyone, 

First time poster here. I'm trying to achieve something that in my head feels like it should be simple but is causing me issues. 

Please see the image below:

In this simple model, I have an orders table and a Date Dimension table with two relationships back to the Orders table ('FunnelDateKey' which is the date an opportunity entered our sales process and 'OrderDateKey' which is the date the order was completed). I need to be able to count the number of orders that entered our sales process and completed an order within any given time period.

The DAX measure I have so far is as follows:

Order Count Test =
VAR ordercount =
    CALCULATE (
        DISTINCTCOUNTNOBLANK ( Orders[Order ID] ),
        USERELATIONSHIP ( Orders[OrderDateKey], 'Date'[DateKey] )
    )
VAR mindate =
    EOMONTH ( MIN ( 'Date'[Date] ), -1 ) + 1
VAR maxdate =
    MAX ( 'Date'[Date] ) + 1
VAR filterdate =
    FILTER ( ALL ( 'Date' ), 'Date'[Date] >= mindate && 'Date'[Date] < maxdate )
VAR result =
    CALCULATE (
        ordercount,
        filterdate,
        USERELATIONSHIP ( 'Date'[DateKey], Appointments[FunnelDateKey] )
    )
RETURN
    result

However, the current result I receive appears to disregard the explicit 'orderdatekey' relationship within the variable 'ordercount'  and overrides the whole measure to use the 'funneldatekey' relationship.

Is there any way to achieve a measure that will filter the order table using the two different relationships within one measure?

Thanks

Bvalen85

[AlexisOlson]

A major problem with your measure is that you are attempting to modify a constant (already calculated) value ordercount inside of another CALCULATE. Once a VAR is defined, it's just like a fixed number in the remainder of the measure.

My basic approach would look something like this:

Order Count Test =
VAR EnteredList =
    CALCULATETABLE (
        DISTINCT ( Orders[Order ID] ),
        USERELATIONSHIP ( Orders[FunnelDateKey], 'Date'[DateKey] )
    )
VAR CompletedList =
    CALCULATETABLE (
        DISTINCT ( Orders[Order ID] ),
        USERELATIONSHIP ( Orders[OrderDateKey], 'Date'[DateKey] )
    )
RETURN
    COUNTROWS ( INTERSECT ( EnteredList, CompletedList ) )

Another approach would be to essentially break the relationship with ALL and apply the filtering manually.

Order Count Test =
CALCULATE (
    DISTINCTCOUNTNOBLANK ( Orders[Order ID] ),
    ALL ( 'Date' ),
    Orders[FunnelDateKey] IN VALUES ( 'Date'[DateKey] ),
    Orders[OrderDateKey] IN VALUES ( 'Date'[DateKey] )
)

[amitchandak]

@Anonymous , In one measure you can not force two userelationship inside one calculate

you can have that in a measure like

example

CALCULATE (
DISTINCTCOUNTNOBLANK ( Orders[Order ID] ),
USERELATIONSHIP ( Orders[OrderDateKey], 'Date'[DateKey] )
) + CALCULATE (
DISTINCTCOUNTNOBLANK ( Orders[Order ID] ),
USERELATIONSHIP ( 'Date'[DateKey], Appointments[FunnelDateKey] )
)

[Anonymous]

Thanks Amitchandak,

I appreciate the response, however I don't think I've described my goal very clearly.

rather than add two measures together to get an overall total, I wish to check the two dates available through relationships to the date table against a timeframe to give me a result. Let's take monthly as an example. Please see below:

Here I have a simplified model. I need to be able to count all the sales in March by OrderDate, but also where the FunnelDate is within the same time period (also March in this example). I have highlighted in green the sales that I want to count, as the FunnelDate is within March, and in orange the sales I don't wish to count (as the FunnelDate is in February). Is there a way to achieve this goal when referencing the same dimdate table through different keys from the Orders Fact table?

Thanks

Bvalen85

[amitchandak]

@Anonymous , Assume FunnelDateKey is the active join, else use it place of OrderDateKey if that is active join

measure =
var _max = maxx(ALLSELECTED('Date'),'Date'[DateKey])
var _min = minx(ALLSELECTED('Date'),'Date'[DateKey])
return
calculate(count(Orders[Order ID] ), filter(Table, Table[OrderDateKey] >=Min && Table[OrderDateKey] <=Max ))

[Anonymous]

Thanks Amitchandak,

Unfortunately this won't work as the key is not in date format, rather it's a hashkey. To replace the key would be to use the date field from the date table, which removes any reference to the relationship I wish to use.

The only date field I can compare to is the date field in the date table, hence the requirement to reference the relationship in use. 

Also, the active relationship is the OrderDateKey relationship for reference.

Any other ideas?

Thanks

Bvalen85