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
     
vojtechsima's avatar
vojtechsima
Icon for Super User rankSuper User
3 years ago

katto16  I am not really sure, what's wrong from your response, 
what the line does, is that it takes current ID from the current ROW and checks the count of that ID in the whole table, and it does for each row and then store it into variable.
Later on I am interested only those IDs who have Count = 1, meaning unique ones, then I check if they also passed and I return 1 or 0.

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

Hi voijtechsima. The code works well. Turns out it's the glitch from my server side. 

Thank you very much for the quick replies and thorough help. I appreciate!