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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Farhat
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.

DeviceDate#ofStarts
A20-Feb-2429
A19-Feb-2428
A18-Feb-2413
A17-Feb-2410
B19-Feb-2419
B18-Feb-246
C20-Feb-2425
D17-Feb-2414
D16-Feb-249
E19-Feb-242

 

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.

Farhat_0-1708962496596.png

 

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.

Farhat_1-1708963221480.png

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.

Farhat_2-1708963664834.png

How can I acieve this? Please help. Thank you! 

 

 

1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @Farhat ,

 

Here are the steps you can follow:

1. enter data -- create table.

vyangliumsft_2-1709007513361.png

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:

 

vyangliumsft_0-1709007415697.png

 

vyangliumsft_1-1709007415699.png

 

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

View solution in original post

4 REPLIES 4
v-yangliu-msft
Community Support
Community Support

Hi  @Farhat ,

 

Here are the steps you can follow:

1. enter data -- create table.

vyangliumsft_2-1709007513361.png

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:

 

vyangliumsft_0-1709007415697.png

 

vyangliumsft_1-1709007415699.png

 

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

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

amitchandak
Super User
Super User

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

Latest Date
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

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

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

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.