cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## How can I make a device to be counted in one bucket instead of multiple buckets?

I want to create a visual in which there are buckets based on the values of #ofStarts column in the X axis and count of Device  on the Y axis but one device should be counted in only one bucket. This bucket should be the one with the maximum value in the #ofStarts column.

Here is the dummy data to explain the issue.

 Device Date #ofStarts A 20-Feb-24 29 A 19-Feb-24 28 A 18-Feb-24 13 A 17-Feb-24 10 B 19-Feb-24 19 B 18-Feb-24 6 C 20-Feb-24 25 D 17-Feb-24 14 D 16-Feb-24 9 E 19-Feb-24 2

On considering a bucket size of 10, we get three buckets for #ofStarts column; 1-10, 11-20 and 21-30. 5 devices have to be sorted in the three buckets. A device has to be put into that bucket against which the #ofStarts has the maximum value for the date range selected in the Date slicer.

For the above data and date range with all the dates, this is how Power BI displays this data.

But Expectation is as shown below. Device has the maximum #ofStarts as 29 and it should be counted in 21-30 bucket and not any other bucket and device B and D have to be counted in 11-20 and in 1-10.

But this has to change when the date slicer is changed. When selecting the date slicer to show data anyday until 18 Feb the following visual is expected.

1 ACCEPTED SOLUTION
Community Support

Hi  @Farhat ,

Here are the steps you can follow：

1. enter data -- create table.

2. Create measure.

``````Max_Group =
MAXX(
FILTER(ALLSELECTED('Table'),
'Table'[Device]=MAX('Table'[Device])),[#ofStarts])``````
``````Measure =
SWITCH(
TRUE(),
MAX('Slicer_Table'[Slicer])="1-10",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=1&&[Max_Group]<=10)),
MAX('Slicer_Table'[Slicer])="11-20",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=11&&[Max_Group]<=20)),
MAX('Slicer_Table'[Slicer])="21-30",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=21&&[Max_Group]<=30)))``````

3. Result:

Best Regards,

Liu Yang

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

4 REPLIES 4
Community Support

Hi  @Farhat ,

Here are the steps you can follow：

1. enter data -- create table.

2. Create measure.

``````Max_Group =
MAXX(
FILTER(ALLSELECTED('Table'),
'Table'[Device]=MAX('Table'[Device])),[#ofStarts])``````
``````Measure =
SWITCH(
TRUE(),
MAX('Slicer_Table'[Slicer])="1-10",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=1&&[Max_Group]<=10)),
MAX('Slicer_Table'[Slicer])="11-20",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=11&&[Max_Group]<=20)),
MAX('Slicer_Table'[Slicer])="21-30",
CALCULATE(DISTINCTCOUNT('Table'[Device]),FILTER(ALLSELECTED('Table'),[Max_Group]>=21&&[Max_Group]<=30)))``````

3. Result:

Best Regards,

Liu Yang

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

Regular Visitor

@v-yangliu-msft I am very grateful to you. You took out the time to look through a lenghty question and provided such a detailed solution. Thanks a lot! May your issues be solved soon too 🙂

Super User

@Farhat , You have bucket the Max value #ofStarts or the the latest value

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

Once you have the measure, you need to segment the measure

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://youtu.be/EyL7KMw877Q

Regular Visitor

@amitchandak Thank you so much for investing your time into putting together the solution piece by piece and sharing all those references with me. This means a lot. And wow you have a youtube channel (https://www.youtube.com/@AmitChandak) as well. You have earned a new subscrier today 🙂