Forum Discussion
measures aggregated on different dimensions
Hi all,
I have a table with datein, dateiout two dimensions. I want to create measure1 aggregated on quantity by datein and measure2 aggregated on price by dateout like the following table. Any idea for this requirement? Thanks in advanced.
Hi lolojee
You can do this using a calendar table which has an active relationship with one of your date columns and an inactive relationship with the other one.
You should set up your data model like this:
And write measures like this:
measure1 = SUM ( Sales[quantity] ) measure2 = CALCULATE ( SUM ( Sales[price] ), USERELATIONSHIP ( Sales[dateout], 'Calendar'[Date] ) )In this example, the inactive relationship is with the dateout column, so measure2 uses USERELATIONSHIP to active the inactive relationship.
Regards,
Owen
2 Replies
- OwenAuger
Super User
Hi lolojee
You can do this using a calendar table which has an active relationship with one of your date columns and an inactive relationship with the other one.
You should set up your data model like this:
And write measures like this:
measure1 = SUM ( Sales[quantity] ) measure2 = CALCULATE ( SUM ( Sales[price] ), USERELATIONSHIP ( Sales[dateout], 'Calendar'[Date] ) )In this example, the inactive relationship is with the dateout column, so measure2 uses USERELATIONSHIP to active the inactive relationship.
Regards,
Owen
- Greg_Deckler
Community Champion
Assuming that you have a disconnected Calendar table and that you use that Calender table for your year/month column in your visual:
measure1 = SUMX(FILTER(ALL('2Dimensions'),MONTH(MAX('Calendar'[Date]))=MONTH([datein])),'2Dimensions'[quantity])
measure2 = SUMX(FILTER(ALL('2Dimensions'),MONTH(MAX('Calendar'[Date]))=MONTH([dateout])),'2Dimensions'[price])
