Hello, friends. I have a problem. I have a data base of production, and every machine entry I have the name of the operator who was producing on this.But, some days, I have two operator in just one machine. Then, the entry come like "A,B", and I just used "separate after delimiter". By the way, this "A or B" operador, can appear in another entry if they went to another machine to produce alone.This way, I have two columns. But I want to count these two columns and show "HOW MANY OPERATORS WORKED THIS DAY" without repeating a operator.

Hi, In the Query Editor, right click on the Operadores column and select Split column. Split by rows there. Create a Calendar Table and build a relationship from the Data column to the Date column of the Calendar Table. Create a slicer from the Date column of the Calendar Table and select any date there. Write this measure =DISTINCTCOUNT('Table1'[Operadores]) Hope this helps.

How to count two columns with names without repeating?

10 Replies

Greg_Deckler
Community Champion
6 years ago
If possible, in Query Editor, unpivot Column 2 and Column 3 and then this should be simple. Otherwise, if for some reason that is not possible, you could do this in DAX:

Distinct Count = VAR __Table = UNION( SELECTCOLUMNS('Table',"Operator 1",[OPERATOR 1]), SELECTCOLUMNS('Table',"Operator 2",[OPERATOR 2]) ) RETURN COUNTROWS(DISTINCT(__Table))
- gabrielrosa
  Frequent Visitor
  6 years ago
  Hello!
  
  Thanks for answer.
  
  But unfortunately didn't worked 😞
  
  Still counting all the values...
  
  I made a measure, is it right?
  
  I just did a measure, is it right?
  - jeroendekker
    Frequent Visitor
    6 years ago
    Hi Gabriel,
    
    I think the issue is that I will add the blanks as a seperate distinct value. Which is why your count is off by 1.
    
    You could try something like this to filter out the empty field value.
    
    DISTINCT = VAR __TABLE = FILTER ( UNION ( VALUES ( 'Table'[operator 1] ); VALUES ( 'Table'[operator 2] ) ); LEN ( [ALFA] ) > 0 ) RETURN COUNTROWS ( __TABLE )
    Best regards,
    
    Jeroen Dekker
- gabrielrosa
  Frequent Visitor
  6 years ago
  I can delete columns 2 and 3, So I have the original entry.
  - Ashish_Mathur
    Super User
    6 years ago
    Hi,
    
    In the Query Editor, right click on the Operadores column and select Split column. Split by rows there. Create a Calendar Table and build a relationship from the Data column to the Date column of the Calendar Table. Create a slicer from the Date column of the Calendar Table and select any date there. Write this measure
    
    =DISTINCTCOUNT('Table1'[Operadores])
    
    Hope this helps.