Forum Discussion

katto16's avatar
katto16
Icon for Helper I rankHelper I
3 years ago
Solved

Count items that appear only once with condition

Hi. I'm trying to count the Unit IDs that appear only once & have the Overall Result of "pass" in Power BI.    Sample data is as below. In this case, there's only 1 which is DS1111. Eventually, I ...
Need Help
  • vojtechsima's avatar
    vojtechsima
    3 years ago

    Hi, man katto16 
    Feel free to add me to your super user list:

    Column = 
var currentID = UniqueStaff[Unit ID]
var currentResult = UniqueStaff[Overall Result]

var CountOfRows = COUNTROWS(FILTER(UniqueStaff, UniqueStaff[Unit ID] = currentID))
var ReturnCheck = IF(CountOfRows = 1 && currentResult = "pass", 1, 0)
return ReturnCheck
     
ddpl's avatar
ddpl
Icon for Solution Sage rankSolution Sage
3 years ago

katto16 ,

below calculated column may also get your result...

 

your need =
var ct = COUNTX(FILTER('Table', 'Table'[Unit ID] = EARLIER('Table'[Unit ID])), 'Table'[Unit ID])
return
IF(
    ct = 1 && 'Table'[Overall Result] = "pass",
    1,
    0
)

 

although I'm not a super user😅

katto16's avatar
katto16
Icon for Helper I rankHelper I
3 years ago

This works great. Thanks for your very prompt help. I appreciate!