count of items that only appear once

[Daniel_G]

Hi,

I'm looking for a formula that will give me a number of items that appear in table only once.

I can easily do it using pivot table with a count of items = 1 filter, but I don't want a list of such items, but just a total count of them.

I tried to do something like X = calculate(count(vendor[No]), count(vendor[No]) = 1), but apparently I can't use count in True/False expressions.

Example: In a table, column 'x' I have following values: a,b,a,b,c,b,d. The result of the calculation in that case would 2 (only c and d appear once).

Please let me know if you know a solution for that.

Thank you in advance,

Daniel

[Anonymous]

Hello Daniel,

try something like

Measure =

VAR varTable =

    ADDCOLUMNS(

        VALUES( vendor[No] ),

        "@Count", 

        VAR varNo = vendor[No]

        VAR varFilterVendor = FILTER( vendor, vendor[No] = varNo )

        RETURN COUNTROWS( varFilterVendor )

    )

VAR varFilterTable =

    FILTER( varTable, [@Count] = 1 )

RETURN

    COUNTROWS( varFilterTable )

I did not test this though, so you might have to have a second look at this.

Regards

Edit: Too late, obviously.

[Greg_Deckler · Accepted solution]

Measure =
  VAR __Table =
    SUMMARIZE(
      'Table',
      [column x],
      "__Count",COUNTROWS('Table')
    )
RETURN
  COUNTROWS(FILTER(__Table,[__Count]=1))

[katto16]

Hi Greg. I'm doing something similar and this code is very useful for me.

Could I ask what if I wanna add more conditions to this? So I managed to get the count of Unit IDs that appear only once by using your code. But I wanna count the ones that appear once & only if they have "pass" result. Simple data table is as below. I want the total count of it eventually. How do I get to that?

Thanks in advance!

[Daniel_G]

Wow, that was quick 🙂

Thanks Greg, works like a charm!