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

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
 
  • katto16's avatar
    katto16
    Icon for Helper I rankHelper I
    3 years ago

    Hey man. Thank you so much for a quick reply. I'm having some issue. So I somewhat get what you did with the code, except from this line and I don't really know how to troubleshoot since I'm still pretty new to Power BI.

    VAR CountofRows = COUNTROWS(FILTER(All_Data_new, All_Data_new[Unit ID] = currentID))

     where All_Data_new is my table. 

    This is your whole code that I put in to insert a new column.

    FPY = 
VAR currentID = 'All_Data_new'[Unit ID]
VAR currentResult = 'All_Data_new'[Overall Result]
VAR CountofRows = COUNTROWS(FILTER(All_Data_new, All_Data_new[Unit ID] = currentID))
VAR ReturnCheck = IF(CountofRows = 1 && currentResult = "pass",1,0)
return ReturnCheck

    So the issue is, it's only returning some of the values and not all. For example, for 15th Nov, there are supposed to be four FPY but according to your code, there is only 1. The condition is correct. The value appears only once and "pass" 

    I just attached my Excel table for the ease of viewing as I can hide un-needed columns.

    • 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!