Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm looking to create a slicer that shows 1, 2, 3, 4, 5 etc. These numbers are generated from the total numbers of a Category (e.g., Poor).
Here is a table: PersonTable
Person | Category | Result | Course |
1 | Poor | 35 | ABC |
1 | Poor | 35 | EFG |
1 | Poor | 45 | HIJ |
1 | Poor | 48 | LMN |
2 | Good | 75 | ABC |
2 | Poor | 45 | EFG |
2 | Great | 85 | HIJ |
2 | Amazing | 95 | LMN |
3 | Good | 72 | ABC |
3 | Good | 73 | EFG |
3 | Good | 77 | HIJ |
3 | Good | 78 | LMN |
4 | Poor | 33 | ABC |
4 | Poor | 44 | EFG |
4 | Poor | 22 | HIJ |
4 | Good | 75 | LMN |
5 | Amazing | 99 | zzz |
5 | Poor | 22 | abc |
5 | Poor | 10 | EFG |
5 | Poor | 33 | LMN |
By creating this Measure = CALCULATE(Count(PersonTable[Person]), PersonTable[Category] = "Poor")
I can produce a table similiar to ...
Person | Measure (Category = Poor) |
1 | 4 |
2 | 1 |
3 | 0 |
4 | 3 |
5 | 3 |
But I'm looking to produce something like this
Number of Poors | Measure (Category = Poor) |
0 | 1 |
1 | 1 |
2 | 0 |
3 | 2 |
4 | 1 |
to create above, I tried playing with SumX, etc.. For my last attempt I was trying to play with:
Once I have the table above, I'm looking to create a slicer that will show in a measure counts. This way I can filter quickly to the people with that have 3 Poors, 2 Poors, and then later 4 Greats, 2 Amazings, etc...
Then by using a slicer, I could reduce this to show only those with 3 Poor, or 4 Poor, etc.
Ugg... slow learning.
Hi @SO ,
Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.
Best Regards
Lucien
Hi @SO ,
The report never populates data, it continues to circle and circle and never update the table. It is not very clear about the details and whether we can provide a sample without confidential data.
Best Regards
Lucien
Hi Lucien,
Thanks so much for this! I am 2 steps closer with your support!
I am able to get some of the data using the Poorcount measure, but I needed to add some additional fields within the Allexcept that coorespond to slicers that I have on the page. As the data is also connected a different table, I needed to add a second filter:
Hi @SO ,
Test the below measure:
poorcount =
IF (
CALCULATE (
COUNTROWS ( PersonTable ),
FILTER (
ALLEXCEPT ( PersonTable, PersonTable[Person] ),
PersonTable[Category] = "Poor"
)
)
= BLANK (),
0,
CALCULATE (
COUNTROWS ( PersonTable ),
FILTER (
ALLEXCEPT ( PersonTable, PersonTable[Person] ),
PersonTable[Category] = "Poor"
)
)
)
Measure = CALCULATE(DISTINCTCOUNT(PersonTable[Person]),FILTER(ALL('PersonTable'),PersonTable[poorcount]=MAXX('PersonTable',PersonTable[poorcount])))
Final get:
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien
@SO , You need to bucket segmentation using an independent table
Bucket/Segment code
measure
Measure = CALCULATE(Count(PersonTable[Person]), PersonTable[Category] = "Poor")
new Table
bucket = Generateseries(1,100,1)
new Measure
Countx(filter(Values(Table[user]), [Measure ] = max(bucket[Value])), [User])
to be used with value of bucket table
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1...
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k
Thank you very much for the response @amitchandak. We are using a Live In-Service connection through Analysis server so I can't create a table. Is there an alternative way to do this through measures only?
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |