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

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    @v-deddai1-msft @amitchandak

    Thanks for your reply. Let me try to make my question clear.
    I have a column year of birth, and I created a calculated column to group the date of birth into groups, for example, 1980 - 1999, 2000 - 2019...and so on. Still, this calculated column gives me a fixed date of the birth group. I want to have a dynamic group, and the grouping changes based on the user's selection on the slicer.

    Imagine that we have a count of customers by their date of birth groups, but then we can change the number of bins in which we have the date of birth groups. Instead of having 1980 - 1999, 2000 - 2019 groups, we split it into 1980 - 1989, 1990 - 1999, or less.

    Is there a way to do this?

    Regards,

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

      Hi Anonymous ,

       

      You can use the following solution but it will only show the start of bin in x-axis:

       

      First create an what-if parameter for bin size:

       

       

      Then create a new birthyear table with birth year column for x-axis and create a relationship between birth year table and fact table:

       

      BirthYear = GENERATESERIES(1900, 2020, 1)

       

       

      Then use the following measure to show count of customers for each bins:

       

      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(_currYear=_binHead,
CALCULATE(
    COUNTROWS(DimCustomer),
BirthYear[Value]>=_binHead && BirthYear[Value]<_binTail
))

       

       

      For more details,please refer to the pbix file:  https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EVg4dzGrdYRImXIYSBQAzRwBOsUmkhEPB1W3RZOLyTT2Gw?e=23QT9s   

       

      And you can also refer to https://radacad.com/dynamic-banding-or-grouping-in-power-bi-using-dax-measures-choose-the-count-of-bins

       

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        I used the measure to count distinct social security numbers, and it gives me the correct values for each row(Birth year group), but it does not display the total amount of count of the customers.

         

         

         

         

        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])
        was _binTail=_binHead+_bandSize
        return
        if(_currYear=_binHead,
        CALCULATE(
        DISTINCTCOUNTNOBLANK(customer[SocialSecurityNo]),
        BirthYear[Value]>=_binHead && BirthYear[Value]<_binTail
        ))