How to distinct count with filter - counting measures

[Anonymous]

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

[Anonymous]

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 · Accepted 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.

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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]

How about:

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

[Anonymous]

No Luck, Its still counting all the rows

[Ashish_Mathur]

Hi,

Does this work?

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

[Ashish_Mathur]

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

[Anonymous]

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

[Anonymous]

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]

@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")
)

[Anonymous]

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

[ebeery]

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

[Anonymous]

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

[Anonymous]

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? 

[ebeery]

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