Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.