Dynamic Buckets based on slicer

[vikash_X_Sinha]

Hi All,

I am currently working on Dynamic buclet based on slicer. i have created calculated column and used selectedvalue to select the slicers which are bin size, Max bin and Min bin. But it is not working.

If i created same calculation in measures which is not acceptable in bar chat.

Please help me on this with example.

Regards,

[v-tsaipranay]

Hi @vikash_X_Sinha ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @vikash_X_Sinha ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. 

Thank you.

[v-tsaipranay · Accepted solution]

Hi @vikash_X_Sinha ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

Dynamic buckets cannot be created using calculated columns because calculated columns are evaluated during model refresh and cannot react to slicers. That is why your SELECTEDVALUE() logic does not work.

To implement dynamic Min/Max/Bin logic, you will need a pre-generated Bucket table (covering the maximum possible bin ranges), and then use measures to calculate the bucket labels and summary values based on slicer selections.

Create a supporting Bucket table (covers all possible values):

Buckets =
GENERATESERIES ( 1, 2000000, 1 )
​

Create a Bucket Range measure: 

Bucket Label =
VAR MinBal = SELECTEDVALUE(MinBalance[Value], 0)
VAR MaxBal = SELECTEDVALUE(MaxBalance[Value], 0)
VAR Bin = SELECTEDVALUE(BinSize[Value], 0)
VAR Current = MAX(Buckets[Value])

RETURN
IF (
    Current >= MinBal && Current <= MaxBal,
    FORMAT( FLOOR((Current - MinBal) / Bin, 1) * Bin + MinBal, "0") 
        & " - " &
    FORMAT( (FLOOR((Current - MinBal) / Bin, 1) * Bin) + Bin + MinBal, "0" ),
    BLANK()
)
​

Create your value measure (e.g. count or sum): 

Total Amount in Bucket =
VAR Label = [Bucket Label]
RETURN
IF(NOT ISBLANK(Label), SUM(Fact[Amount]))
​

Place this in your bar chart on X-axis → Bucket Label (measure) and on Y-axis → Total Amount in Bucket (measure) now apply slicers for Min, Max, and Bin Size.

If it's still doesn't work please share a small sample dataset and the expected bucket output. I can then create the exact DAX and PBIX structure for your scenario.

Thank you.

[vikash_X_Sinha]

Hi  , 

this is the format i want to show in bar visual 

i am using  3 selected slicers  bin size , min balance and max balance 

min balance is starting of bar char like 2 or 5 .. etc

max is end of bar like 40,000 or 50,000 ..etc  selected range 

bin size is show the values between the binsize if we select 5000 then it should show 1-5000 in on bar , 5000 to 10000 till the max balance selected 

i have tried but it is not working 

Bin Size =

SELECTCOLUMNS(

GENERATESERIES(1000, 100000, 1000),

"Value", ROUND([Value],2)

   )

Max Balance =

SELECTCOLUMNS(

GENERATESERIES(10000, 2000000, 10000),

"Value", ROUND([Value],2)

   )

Min Balance =

SELECTCOLUMNS(

GENERATESERIES(1, 100000, 1),

"Value", ROUND([Value],2)

   )

using this dax for min and max bin size  disconnected table 

this is what i am trying to build

@Zanqueta

[lbendlin]

Power BI does not support dynamic bucketing.  You need to have a predefined list of buckets (of varying sizes, if needed), and then use measures to pick and fill them.

[Zanqueta]

Hi @vikash_X_Sinha, could you please provide sample data without any personal information? This will help us better understand the issue. Alternatively, you can share a screenshot to illustrate the error.

[vikash_X_Sinha]

HI @Zanqueta 

Have you got any idea about the data and screen shot i have shared with you.

[lbendlin]

I don't see any shared data.  Have you created the reference table for your bucket names and boundaries?