Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
@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))
Thank you. Unfortnately, my date keys are actual keys and not date values. Is there a way to use USERELATIONSHIP inside a filter statement?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
135 | |
105 | |
104 | |
80 | |
65 |