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 want to add them and need the total number of items that pass and appear only once. 

 

Thanks in advance!

 

Just @ing super users here: Greg_Deckler amitchandak Ashish_Mathur Jihwan_Kim mangaus1111 

 

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
     

6 Replies

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

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