Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
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
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])
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
User | Count |
---|---|
98 | |
90 | |
83 | |
70 | |
67 |
User | Count |
---|---|
113 | |
104 | |
101 | |
73 | |
65 |