Forum Discussion

ValentinBIA's avatar
ValentinBIA
Resolver I
7 years ago
Solved

Filter Count

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...
Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    this should do the trick

    Measure =
VAR RentalsWithStations =
    ADDCOLUMNS (
        Rentals,
        "NrOfStations", CALCULATE (
            COUNT ( Stations[Station_ID] ),
            'Calendar'[Date] <= EARLIER ( Rentals[Date] )
        )
    )
RETURN
    SUMX (
        GROUPBY (
            RentalsWithStations,
            [NrOfStations],
            "NrOfRentals", COUNTX ( CURRENTGROUP (), [Rental_ID] )
        ),
        DIVIDE ( [NrOfRentals], [NrOfStations] )
    )

    BTWin your axample Stations has US date format, and Rentals is dd/mm/yyyy, right? Otherwise I cannot reconcile the numbers with your example

  • v-chuncz-msft's avatar
    v-chuncz-msft
    7 years ago

    ValentinBIA,

     

    You may refer to the DAX below.

    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] )
                )
            )
        )
    )
)
ValentinBIA's avatar
ValentinBIA
Resolver I
7 years ago

Stachu, v-chuncz-msft, thank you very much to both of you!

 

So, I adjusted the relationships, and both of your solutions work perfectly fine!

 

However, and I should have be more precise (I did not think it would be an issue), I also filter sometimes by the region or town the stations are located in. And whenever I do so, your measure don't work anymore (as you did not have this info when writting them).

 

Do you think of a tweak that would allow me to filter by town and region? Or do you need more dummy data to show you exactly what I mean?

 

Best,

 

Valentin

ValentinBIA's avatar
ValentinBIA
Resolver I
7 years ago

Hey Stachu and v-chuncz-msft, any idea on how to adjust the formula? Or should I just make another post?