Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Dynamic grouping

Hi, I have a column date of birth, and I created a calculated column to create age groups, for example, 1951 - 1960, 1961 - 1770, and so on, but I want to have a dynamic age grouping where users can set the parameters, How could I do that?

 

Regards,

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hello @Samrawit21 ,

    Use the following measure:

    Count of Customers in each Bin =
VAR _bandSize = Bin[Bin Value]
VAR _YearbinTable =
    GENERATESERIES (
        MINX ( ALL ( BirthYear ), BirthYear[Value] ),
        MAXX ( ALL ( BirthYear ), BirthYear[Value] ),
        _bandSize
    )
VAR _currYear =
    SELECTEDVALUE ( BirthYear[Value] )
VAR _binHead =
    MAXX ( FILTER ( _YearbinTable, [Value] <= _currYear ), [Value] )
VAR _binTail = _binHead + _bandSize
RETURN
    IF (
        HASONEVALUE ( BirthYear[Value] ),
        IF (
            _currYear = _binHead,
            CALCULATE (
                COUNTROWS ( DimCustomer ),
                BirthYear[Value] >= _binHead
                    && BirthYear[Value] < _binTail
            )
        ),
        COUNTROWS ( DimCustomer )
    )

    If this post helps, then consider Accept it as the solution to help other members find it faster.

    Best regards

    Dedmon Dai

8 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak  Thank you for your reply!
      I have a measure that counts the distinct number of customers, and I created a measure to group them by date of birth,

      Age groups         No of customers
      1950 - 1960        98 

      1960 -1970         85 
      ...
      but what I want to have is a way to allow users to set the age groups; for example, they might want to see the number of customers in different age groups,
      For example, 1956 - 1975
      1976- ....
      Is there a way to do that?

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        5 years ago

        Hi Anonymous ,

         

        You can create a year column:

         

        Birth Year = YEAR('Table'[date of birth])

         

        Use it as slicer and you can use the following measure to count No of customers:

         

        No of customer = COUNTROWS('Table')

         

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai