Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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))
I can delete columns 2 and 3, So I have the original entry.
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.
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
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 |
Hi,
Did you try my solution?
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.
You are welcome.
Hello!
Thanks for answer.
But unfortunately didn't worked 😞
Still counting all the values...
I made a measure, is it right?
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
User | Count |
---|---|
97 | |
87 | |
78 | |
74 | |
70 |
User | Count |
---|---|
113 | |
105 | |
84 | |
65 | |
64 |