How to count two columns with names without repeating?

[gabrielrosa]

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.

[Greg_Deckler]

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]

I can delete columns 2 and 3, So I have the original entry.

[Ashish_Mathur · Accepted solution]

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.

[Greg_Deckler]

Can you post sample data as text so I can test? Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[gabrielrosa]

Ok! Here it is.

DATA	MÁQUINA	OPERADORES	PRODUÇÃO
10/01/2020	1	A,B	1
10/01/2020	2	C	1
10/01/2020	3	D	1
10/01/2020	4	E	1
10/01/2020	5	A	1
10/01/2020	6	B	1
11/01/2020	1	A	1
11/01/2020	2	C,B	1
11/01/2020	3	D	1
11/01/2020	4	E	1
11/01/2020	5	B	1
null	null	null	null

[Ashish_Mathur]

Hi,

Did you try my solution?

[gabrielrosa]

Hey, Ashish! 

It worked!

Thank you so much for the insight!

However, I have to create a copy of the database with only the dates and names of the operators, cause dividing the row, the production column is divided too, like this:

DATE	OPERATOR	PRODUCTION
16/03	A , B	10

After dividing:

DATE	OPERATOR	PRODUCTION
16/03	A	10
16/03	B	10

But no problem. It's a solution!

Thank you so much.

[Ashish_Mathur]

You are welcome.

[gabrielrosa]

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]

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