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] )
                )
            )
        )
    )
)
Stachu's avatar
Stachu
Community Champion
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

  • ValentinBIA's avatar
    ValentinBIA
    Resolver I
    7 years ago

    Hello Stachu, thank you very much for taking the time to try to solve my problem!

     

    You are right, I made a mistake when I wrote the dummy data, but indeed the rentals are dd/mm/yyyy, and stations have US format.

     

    I implemented your measure in my file, but currently it gives me to the total number of rentals, not the average per station.

    Do you have any idea of what I could try to change?

     

    Thank you!

     

    Best,

     

    Valentin

    • Stachu's avatar
      Stachu
      Community Champion
      7 years ago

      are you using date from Calendar table in the visual and the slicer?
      Calendar should be joined with both tables by 1:many reliationship with single direction

      • 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