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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

How to distinct count with filter - counting measures

Hello, 

 

Im trying to create a bar chart that counts how many of my Items are either A, B or C 

 

I've tried the following fomula:

CountA = COUNTROWS(FILTER('Table', [Chart] = "A" ))
CountB = COUNTROWS(FILTER('Table', [Chart] = "B" ))
 
The issue with the formula above is that it counts all rows (thousands) instead of just 1 each distinct item, so I tried this formula: 
 
CountA = CALCULATE(
DISTINCTCOUNT('table'[Item]),
FILTER('Table', [Chart] = "A" ))
 
CountB = CALCULATE(
DISTINCTCOUNT('table'[Item]),
FILTER('Table', [Chart] = "B" ))
The formula above seems to only count the distinct amount of 'item' there are but its not counting how many have A, B, C.
 
I feel like Im close but I cant figure the correct fomula that will work. Help!
 
EDIT: 'Chart' is created by a measure of an if statement i.e. if([value]>10, "A", "B"). I think this is where the issue is, I wonder if its possible to count measures? 
 
 
Thank you
 
 
 
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous ,

 

Oh ,got it!

I built another data sample :

Eyelyn9_0-1649652645191.png

And the [Chart] is a measure not column:

Chart = IF(MAX('Table'[Column1])>10,"A","B") 

Then please try:

Count A = 
var _t=SUMMARIZE('Table',[Index],"Chart",[Chart])
return COUNTROWS(FILTER(_t,[Chart]="A"))
Count B = 
var _t=SUMMARIZE('Table',[Index],"Chart",[Chart])
return COUNTROWS(FILTER(_t,[Chart]="B"))

Output:

Eyelyn9_1-1649652711936.png

 

Best Regards,
Eyelyn Qin
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

17 REPLIES 17
Anonymous
Not applicable

Hi @Anonymous ,

 

1.Since the measure returns a summarized value , we could not directly reference it for calculations later.

So in my measure, I used SUMMARIZE() to add [measure] in a table grouped by [Index]. In this case, each [Index] will have the corresponding [Chart].

 

2. "Chart" is just a name or said alias for [Chart], you could replace it with anything, like "XX":

Count A = 
var _t=SUMMARIZE('Table',[Index],"XX",[Chart])
return COUNTROWS(FILTER(_t,[XX]="A"))

 

Best Regards,
Eyelyn Qin

Anonymous
Not applicable

Hi @Anonymous ,

 

Oh ,got it!

I built another data sample :

Eyelyn9_0-1649652645191.png

And the [Chart] is a measure not column:

Chart = IF(MAX('Table'[Column1])>10,"A","B") 

Then please try:

Count A = 
var _t=SUMMARIZE('Table',[Index],"Chart",[Chart])
return COUNTROWS(FILTER(_t,[Chart]="A"))
Count B = 
var _t=SUMMARIZE('Table',[Index],"Chart",[Chart])
return COUNTROWS(FILTER(_t,[Chart]="B"))

Output:

Eyelyn9_1-1649652711936.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Perfect, thank you! can I ask what that first line _t is doing, escpecially the "chart", [chart] part? 

Anonymous
Not applicable

Hi @Anonymous ,

 

If you want to calculate the count of each Chart, I have done it in two ways, please check.

 

1. Use the summarize type of "Count":

Eyelyn9_0-1649390815954.png

2. Create a Count measure:

Count Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Chart]) )

Output:

Eyelyn9_1-1649390886749.png

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

No luck unfortunetly. But to add more context 'Chart' is a measure calculated by an if statement i.e. if [value] > 10, "A" , "B" 

adrianc2
Regular Visitor

How about:

 

CountA = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "A" ))
CountB = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "B" ))
Anonymous
Not applicable

No Luck, Its still counting all the rows

Ashish_Mathur
Super User
Super User

Hi,

Does this work?

Measure = CALCULATE(DISTINCTCOUNT('table'[Item]),'Table'[Chart] = "A"||'Table'[Chart] = "B"||'Table'[Chart] = "C")


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

That is strange.  Share the link from where i can download your PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

Cant, sorry. But does it help to point out that 'chart' is a measure from a datediff measure then an if statement

Anonymous
Not applicable

It gives the error "A function 'Calculate' has been used in a True/False expression that is used as a table filter expression. This is not allowed."

ebeery
Memorable Member
Memorable Member

@Anonymous can you give an example of what you mean when you say "its not counting how many have A, B, C"?

Your code is working fine in my testing.

 

 

CountA = 
CALCULATE(
    DISTINCTCOUNT('Table'[Item]),
    FILTER('Table', [Chart] = "A")
)

 

CountB = 
CALCULATE(
    DISTINCTCOUNT('Table'[Item]),
    FILTER('Table', [Chart] = "B")
)

 

ebeery_1-1649203118976.png

 

 

Anonymous
Not applicable

Does it help to point out that 'chart' is a measure from a datediff measure then an if statement

Yes, that would've been very helpful to know beforehand.  Your approach using CALCULATE is not going to work then.

Anonymous
Not applicable

Oh I see, do you have suggestions on what other approach I can try? 

Anonymous
Not applicable

I did each measure individually, maybe thats why. Im getting a red underline right now with CountB and CountC, How would I write this in full? 

@Anonymous I'm sorry I wasn't clear - those should be written as separate measures.  I've edited to clarify.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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