Counting a Measure and Using the Counts in a Slicer

Question

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:

#MeasureCount= sumx (SUMMARIZE(PersonTable, [Category], [Person], "Tot", Sum(PersonTable[Category]), IF ([Measure] >=4, 4, [Measure])

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.

amitchandak · Answer

SO , You need to bucket segmentation using an independent table 
&nbsp;
Bucket/Segment codemeasure Measure =&nbsp; CALCULATE(Count(PersonTable[Person]), PersonTable[Category] = "Poor")
new Table bucket = Generateseries(1,100,1)
&nbsp;
new Measure Countx(filter(Values(Table[user]), [Measure ] = max(bucket[Value])), [User])
&nbsp;
to be used with value of bucket table
&nbsp;
Dynamic Segmentation Bucketing Binninghttps://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

so · Answer

Thank you very much for the response&nbsp;amitchandak.&nbsp; We are using a Live In-Service connection through Analysis server so I can't create a table.&nbsp; Is there an alternative way to do this through measures only?&nbsp;&nbsp;

v-luwang-msft · Answer

Hi&nbsp;SO&nbsp;，
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:&nbsp;

&nbsp;
Did I answer your question? Mark my post as a solution!
Best Regards
Lucien

so · Answer

Hi Lucien,&nbsp;&nbsp;Thanks so much for this!&nbsp; I am 2 steps closer with your support!&nbsp; &nbsp;&nbsp;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.&nbsp; As the data is also connected a different table, I needed to add a second filter:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;FILTER (ALLSELECTED(location), location[LocationType] = "S" ).&nbsp; This seems to have worked.&nbsp;I am not able to get the measure to work.&nbsp; It is not that it not working, but the report never populates data, it continues to circle and circle and never update the table.&nbsp; I also tried to reduce the data in this measure by adding more fields to the AllEXCEPT, but that did not seem to make a difference either.&nbsp;&nbsp;&nbsp;Any thoughts?&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;v-luwang-msft&nbsp;&nbsp;

v-luwang-msft · Answer

Hi&nbsp;SO&nbsp;，
The report never populates data, it continues to circle and circle and never update the table.&nbsp;It is not very clear about the details and whether we can provide a sample without confidential data.
&nbsp;
&nbsp;
Best Regards
Lucien

Forum Discussion

Counting a Measure and Using the Counts in a Slicer

6 Replies

Related content

Count Measure

Counting nulls

Count Of Drivers

count distinct

Create count measure