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

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
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

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