Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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:
Solved! Go to Solution.
Hi @Anonymous ,
Oh ,got it!
I built another data sample :
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:
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.
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
Hi @Anonymous ,
Oh ,got it!
I built another data sample :
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:
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.
Perfect, thank you! can I ask what that first line _t is doing, escpecially the "chart", [chart] part?
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":
2. Create a Count measure:
Count Measure = CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Chart]) )
Output:
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.
No luck unfortunetly. But to add more context 'Chart' is a measure calculated by an if statement i.e. if [value] > 10, "A" , "B"
How about:
CountA = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "A" ))
CountB = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "B" ))
No Luck, Its still counting all the rows
Hi,
Does this work?
Measure = CALCULATE(DISTINCTCOUNT('table'[Item]),'Table'[Chart] = "A"||'Table'[Chart] = "B"||'Table'[Chart] = "C")
That is strange. Share the link from where i can download your PBI file.
Cant, sorry. But does it help to point out that 'chart' is a measure from a datediff measure then an if statement
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."
@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")
)
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.
Oh I see, do you have suggestions on what other approach I can try?
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
72 | |
71 | |
57 | |
38 | |
36 |
User | Count |
---|---|
83 | |
67 | |
62 | |
46 | |
45 |