Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Filter fact table by two date dimensions at the same time (using USERELATIONSHIP)

Hi, I have an Orders fact table that has an order date and a shipped date keys that are related to the same Date dimension table. 

The order date is the active relationship and the shipped date is inactive. 

I can get a count of orders by order date using:

 

Order Count = COUNTROWS(Orders)

 


And a count by shipped date using:

 

Shipped Order Count = CALCULATE([Order Count], USERELATIONSHIP(Order[ShipedDateKey], 'Date'[DateKey])

 

 

But how can I show orders that were placed and shipped in the same period? How can I use both relationships at the same time?

 

Another important point is that DateKey and ShippedDateKey are real keys and not dates. So I can't filter based on those columns directly. 


Thank you for your help!

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous ,

    Try a measure like

    placed and shipped in the same period =
    var _min = maxx(allselected(date),date[date])
    var _max = maxx(allselected(date),date[date])
    return
    calculate([ Order Count ], filter(Order, Order[ShipedDateKey]<=_max && Order[ShipedDateKey]>-_min))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thank you. Unfortnately, my date keys are actual keys and not date values. Is there a way to use USERELATIONSHIP inside a filter statement?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    Ideally the relationship should be between proper date columns.  Also, what do you mean by "show orders that were placed and shipped in the same period"?  Share some data, define same period and show the expected result.