The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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.
Solved! Go to 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.
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.
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.
hi, @kballar1
i think you need switch() fumnction
like
newcolumn =
)
@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
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.
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
12 | |
8 | |
8 |