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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SO
Helper III
Helper III

Counting a Measure and Using the Counts in a Slicer

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 
1Poor35ABC
1Poor35EFG
1Poor45HIJ
1Poor48LMN
2Good75ABC
2Poor45EFG
2Great85HIJ
2Amazing95LMN
3Good72ABC
3Good 73EFG
3Good 77HIJ
3Good78LMN
4Poor33ABC
4Poor44EFG
4Poor22HIJ
4Good75LMN
5Amazing99zzz
5Poor22abc
5Poor10EFG
5Poor33LMN

 

By creating this Measure = CALCULATE(Count(PersonTable[Person]), PersonTable[Category] = "Poor")

 I can produce a table similiar to ...

Person   Measure (Category = Poor)
14
21
30
43
53

 

But I'm looking to produce something like this 

 

Number of Poors Measure (Category = Poor)
01
11
20
32
41

 

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.

 

 

 

 

6 REPLIES 6
v-luwang-msft
Community Support
Community Support

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

v-luwang-msft
Community Support
Community Support

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

SO
Helper III
Helper III

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:         

FILTER (ALLSELECTED(location), location[LocationType] = "S" ).  This seems to have worked.
 
I am not able to get the measure to work.  It is not that it not working, but the report never populates data, it continues to circle and circle and never update the table.  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.  
 
Any thoughts?  
 
 

 

v-luwang-msft
Community Support
Community Support

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: 

vluwangmsft_0-1645518078762.png

 

Did I answer your question? Mark my post as a solution!


Best Regards

Lucien

amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.