Dynamic grouping

[Anonymous]

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,

[amitchandak]

@Anonymous , Dynamic Grouping, means you required that on a measure. For that, you need an independent table and a new measure that will use that independent table.

I discussed this approch in this video see if that can help :https://youtu.be/CuczXPj0N-k

or

https://www.daxpatterns.com/dynamic-segmentation/
https://radacad.com/grouping-and-binning-step-towards-better-data-visualization

[Anonymous]

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

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]

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

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]

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

))

[v-deddai1-msft · Accepted solution]

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]

I have created a what-if parameter for bin size:  to dynamically group the count of customers based on user selection,  and the grouping changes based on the user's selection on the slicer(Bin)

and used the following measure  to show these dynamically grouped values based on a date selection,
I have created a date table and connected the date with the start date and end date of Dimcustomer  with inactive relationships, 

What I want to have is when I select a date( from the date table), I want to get counts of customers where the Start date is less than the selected date and the End date is greater than the selected date 
For example, if I select A date 2029-12-23, then I want to get the count of customers
where start date < '2029-12-23' and end date > '2029-12-23'

I am using 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 ( DISTINCTCOUNT(DimCustomer[CustomerKey] ), BirthYear[Value] >= _binHead && BirthYear[Value] < _binTail,FILTER(FILTER(DimCustomer,DimCustomer[start date]<max('Date'[Date]) && (DimCustomer[end date] >max('Date'[Date]))) ) ), CALCULATE(DISTINCTCOUNT(DimCustomer[CustomerKey]),FILTER(DimCustomer,DimCustomer[start date]<max('Date'[Date]) && (DimCustomer[end date] >max('Date'[Date])))))

Please see the example below. If the bin value is 5,
when I filter by date(date has an inactive relationship with the start date and end date)

It gives me values only for years listed in the BirthYear column, e.g., 1930.1935,1940. It does not calculate the count of customers between birthday groups For example  It does not count the number of customers between the ages of 1930 - 1935 it only counts customers for age 1930. but the total gives the correct count