cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ValentinBIA
Resolver I
Resolver I

Filtering by region/town on top of date

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_IDOpening dateCityRegion
A1/1/2018WinterfellNorth
B1/1/2018WhiterunNorth
C2/1/2018HogwartSouth
D4/1/2018HogwartSouth
E4/1/2018WhiterunNorth
F5/1/2018WinterfellNorth

 

And here is the rentals table:

Rental_IDDate
15/1/2018
29/1/2018
320/1/2018
420/1/2018
522/1/2018
629/1/2018
72/2/2018
85/2/2018
95/2/2018
1025/2/2018
119/4/2018
1211/4/2018
1321/4/2018
143/5/2018
1513/5/2018
1615/5/2018
1715/5/2018
1819/5/2018
1925/5/2018
2025/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!

1 REPLY 1
v-cherch-msft
Microsoft
Microsoft

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

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.