Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
Measure =
VAR __Table =
SUMMARIZE(
'Table',
[column x],
"__Count",COUNTROWS('Table')
)
RETURN
COUNTROWS(FILTER(__Table,[__Count]=1))
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.
Measure =
VAR __Table =
SUMMARIZE(
'Table',
[column x],
"__Count",COUNTROWS('Table')
)
RETURN
COUNTROWS(FILTER(__Table,[__Count]=1))
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!
Wow, that was quick 🙂
Thanks Greg, works like a charm!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |