Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
kballar1
Helper I
Helper I

Aging Bucket Slicer

@Dangar332 

 

I can't seem to find what works for what I currently need.

I am needing to create a simple aging bucket on a drop-down slicer, based on days

0-2

3-6

7-10

10+ 

 

please advise. Thank you.

 

2 ACCEPTED SOLUTIONS

Hi @kballar1 

 

Using "Enter Data", I created a table of buckets with a sort order.  Using "sort by column", I sorted [Lag Bucket] by [sort order].

 

Also, I used the calculated column by @Dangar332  .

 

I then wrote this measure:

 

Bucket Count = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        'Table'[newcolumn] = SELECTEDVALUE( 'Lag Buckets'[Lag Bucket] )
    )

 

 

Let me know what you think.

 

Lag Buckets.pbix

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

View solution in original post

v-weiyan1-msft
Community Support
Community Support

Hi @kballar1 ,

 

Based on your description, Please try the following steps:
My Sample:

vweiyan1msft_1-1706176073511.png

1. Please try code as below to Create a Calculated table.

Table 2 = DATATABLE("Index",INTEGER,
                    "Bucket",STRING,
                    "Min",INTEGER,
                    "Max",INTEGER,
                    {{1,"0-2",0,2},
                     {2,"3-6",3,6},
                     {3,"7-10",7,10},
                     {4,"10+",11,999}
                    })

vweiyan1msft_2-1706176116960.png

2. Use the following code to create a Measure.

Measure = IF(SELECTEDVALUE('Table'[Days]) >= MAX('Table 2'[Min]) && SELECTEDVALUE('Table'[Days]) <= MAX('Table 2'[Max]),
            1,
            IF(SELECTEDVALUE('Table 2'[Bucket]) = BLANK(),1,-1))

3. Select your visual object, put the measure in the “Filters on this visual” section, and filter it by “Measure is 1”.

vweiyan1msft_3-1706176163287.png

The field of the slicer is from Table 2. When you select "0-2" in the slicer, Result is as below.

vweiyan1msft_4-1706176181495.png

 

Best Regards,
Yulia Yan

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

View solution in original post

4 REPLIES 4
v-weiyan1-msft
Community Support
Community Support

Hi @kballar1 ,

 

Based on your description, Please try the following steps:
My Sample:

vweiyan1msft_1-1706176073511.png

1. Please try code as below to Create a Calculated table.

Table 2 = DATATABLE("Index",INTEGER,
                    "Bucket",STRING,
                    "Min",INTEGER,
                    "Max",INTEGER,
                    {{1,"0-2",0,2},
                     {2,"3-6",3,6},
                     {3,"7-10",7,10},
                     {4,"10+",11,999}
                    })

vweiyan1msft_2-1706176116960.png

2. Use the following code to create a Measure.

Measure = IF(SELECTEDVALUE('Table'[Days]) >= MAX('Table 2'[Min]) && SELECTEDVALUE('Table'[Days]) <= MAX('Table 2'[Max]),
            1,
            IF(SELECTEDVALUE('Table 2'[Bucket]) = BLANK(),1,-1))

3. Select your visual object, put the measure in the “Filters on this visual” section, and filter it by “Measure is 1”.

vweiyan1msft_3-1706176163287.png

The field of the slicer is from Table 2. When you select "0-2" in the slicer, Result is as below.

vweiyan1msft_4-1706176181495.png

 

Best Regards,
Yulia Yan

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

Dangar332
Super User
Super User

hi, @kballar1 

i think you need switch() fumnction
like

newcolumn = 

Column =
SWITCH(TRUE(),
'Table'[Column1]>=0 && 'Table'[Column1]<4,"0-3",
'Table'[Column1]>=4 && 'Table'[Column1]<8,"4-7",
'Table'[Column1]>=8 && 'Table'[Column1]<11,"8-10","10"
)



)

@Dangar332 
Is there a way that I can put the bucket dates in order so 10 is at the bottom and the last one says 10+

 

Thank you 

IMG_1851.png

Hi @kballar1 

 

Using "Enter Data", I created a table of buckets with a sort order.  Using "sort by column", I sorted [Lag Bucket] by [sort order].

 

Also, I used the calculated column by @Dangar332  .

 

I then wrote this measure:

 

Bucket Count = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        'Table'[newcolumn] = SELECTEDVALUE( 'Lag Buckets'[Lag Bucket] )
    )

 

 

Let me know what you think.

 

Lag Buckets.pbix

 



Proud to be a Super User!

daxformatter.com makes life EASIER!

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.