Aging Bucket Slicer

[kballar1]

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

[gmsamborn · Accepted solution]

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

[v-weiyan1-msft · Accepted solution]

Hi @kballar1 ,

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

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}
                    })

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

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

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]

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"

)

)

[kballar1]

@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 

 

[gmsamborn · Accepted solution]

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