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 per station.

However, the number of stations that I have is not fixed. It increased over time.

 

Station_IDOpening date
A1/1/2018
B1/1/2018
C2/1/2018
D4/1/2018
E4/1/2018
F5/1/2018

 

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

 

What I currently have is measure that gives me the average number of rentals by stations, but considers that there are 6 stations at all times.

Avg_rentals = DIVIDE(COUNT(Rentals[Rental_id]),COUNTX(Stations,Stations[Station_ID))

I use slicers to filter rentals by date (and also by city, but not explained in this example).

For example is if I select date from 1/1/2018 to 3/1/2018 (US format), I want the measure to give me the result:

Average rentals = 4.33 (which is 6 rentals / 2 Stations + 4 rentals / 3 Stations)

 

Can anyone help me with that?

 

Thank you very much!

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] )
                )
            )
        )
    )
)

7 Replies

  • 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

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

    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.

     

    Station_IDOpening date
    A1/1/2018
    B1/1/2018
    C2/1/2018
    D4/1/2018
    E4/1/2018
    F5/1/2018

     

    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

     

    What I currently have is measure that gives me the average number of rentals by stations, but considers that there are 6 stations at all times.

    Avg_rentals = DIVIDE(COUNT(Rentals[Rental_id]),COUNTX(Stations,Stations[Station_ID))

    I use slicers to filter rentals by date (and also by city, but not explained in this example).

    For example is if I select date from 1/1/2018 to 3/1/2018 (US format), I want the measure to give me the result:

    Average rentals = 4.33 (which is 6 rentals / 2 Stations + 4 rentals / 3 Stations)

     

    Can anyone help me with that?

     

    Thank you very much!