cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ValentinBIA Regular Visitor
Regular Visitor

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
Community Support Team
Community Support Team

Re: Filtering by region/town on top of date

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.