Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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 A 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 E 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.
How can I acieve this? Please help. Thank you!
Solved! Go to Solution.
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
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
@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 🙂
@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
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 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
112 | |
89 | |
82 | |
76 | |
64 |
User | Count |
---|---|
142 | |
111 | |
108 | |
99 | |
95 |