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.
Good morning Power BI community!
I'm running into some problem building my model, and I was wondering is you guys could help.
I want to have a Card that gives me the average number of rentals per station.
However, the number of stations that I have is not fixed. It increased over time.
Moreover, I want to be able to filter by region/town on top of date filtering.
Station_ID | Opening date | City | Region |
A | 1/1/2018 | Winterfell | North |
B | 1/1/2018 | Whiterun | North |
C | 2/1/2018 | Hogwart | South |
D | 4/1/2018 | Hogwart | South |
E | 4/1/2018 | Whiterun | North |
F | 5/1/2018 | Winterfell | North |
And here is the rentals table:
Rental_ID | Date |
1 | 5/1/2018 |
2 | 9/1/2018 |
3 | 20/1/2018 |
4 | 20/1/2018 |
5 | 22/1/2018 |
6 | 29/1/2018 |
7 | 2/2/2018 |
8 | 5/2/2018 |
9 | 5/2/2018 |
10 | 25/2/2018 |
11 | 9/4/2018 |
12 | 11/4/2018 |
13 | 21/4/2018 |
14 | 3/5/2018 |
15 | 13/5/2018 |
16 | 15/5/2018 |
17 | 15/5/2018 |
18 | 19/5/2018 |
19 | 25/5/2018 |
20 | 25/5/2018 |
I already use this measure to filter by date, but filtering by town/region does not seem to work correctly.
Measure = SUMX ( SUMMARIZE ( 'Calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ), CALCULATE ( DIVIDE ( COUNTROWS ( Rentals ), COUNTROWS ( FILTER ( ALL ( Stations ), Stations[Opening date] <= MAX ( 'Calendar'[Date].[Date] ) ) ) ) ) )
Can anyone help me with that?
Thank you very much!
Hi @ValentinBIA
You may refer to below measure:
Measure = SUMX ( SUMMARIZE ( 'calendar', 'Calendar'[Date].[Year], 'Calendar'[Date].[Month] ), CALCULATE ( DIVIDE ( COUNTROWS ( Rentals ), CALCULATE ( COUNTROWS ( Stations ), FILTER ( ALLEXCEPT ( Stations, Stations[Region] ), MONTH ( Stations[Opening date] ) <= MONTH ( MAX ( Stations[Opening date] ) ) && Stations[Opening date] <= MAX ( 'calendar'[Date].[Date] ) ) ) ) ) )
Regards,
Cherie
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 | |
80 | |
67 | |
63 |
User | Count |
---|---|
147 | |
110 | |
108 | |
85 | |
64 |