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] )
                )
            )
        )
    )
)
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
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] )
                )
            )
        )
    )
)