Forum Discussion

gabrielrosa's avatar
gabrielrosa
Frequent Visitor
6 years ago
Solved

How to count two columns with names without repeating?

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...
Data Protection
General Comment
Need Help
Show and Tell
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    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.

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity 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's avatar
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's avatar
    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