Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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
 
 
 
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

17 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Does this work?

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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."

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      4 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

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

  • adrianc2's avatar
    adrianc2
    Regular Visitor
    4 years ago

    How about:

     

    CountA = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "A" ))
CountB = CALCULATE( COUNTROWS('Table'),FILTER('Table', [Chart] = "B" ))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      No Luck, Its still counting all the rows

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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