Comparing Values across four different columns

[Anonymous]

Hi Team

So I have Four Columns which contains Employee ID's I have filtered it out  using a project Completed Date.

I want DIstinct count of Employee ID's across all four columns ?

How to Proceed ahead with it

[v-luwang-msft]

Hi @Anonymous ，

But according to what you mentioned before, isn't what you want is the sum of the four columns of data combined and then not duplicated?

Best Regards

Lucien

[Anonymous]

yes I want that but let me tell you how so if you take these four columns and put in an excel sheet one below the other in same column and then remove duplicate you will get a distinct count of employee id ...

that would be the correct result .

[v-luwang-msft]

Hi @Anonymous ,

Pls test the below measure:

COUNT =
COUNTROWS (
    DISTINCT (
        UNION (
            CALCULATETABLE ( VALUES ( Sheet1[IdeaId] ), ALLSELECTED ( Sheet1 ) ),
            CALCULATETABLE ( VALUES ( Sheet1[IChampID] ), ALLSELECTED ( Sheet1 ) ),
            CALCULATETABLE ( VALUES ( Sheet1[ISpocID] ), ALLSELECTED ( Sheet1 ) ),
            CALCULATETABLE ( VALUES ( Sheet1[Idea Owner EmpId] ), ALLSELECTED ( Sheet1 ) )
        )
    )
)

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[Anonymous]

Hi, 

It didn't work 

 it is just giving an over all count. As you saw in raw data some emp id repeats b/w these column .

[v-luwang-msft]

Hi @Anonymous ，

Not clearly.Could you pls share a sample data and expected output?Remember to remove confidential data.

Best Regards

Lucien

[Anonymous]

https://1drv.ms/x/s!Aviuz_Y60KcRg_0-pS3BdwdYwye4LQ?e=tNbfxb

You can get excel File Here

[Anonymous]

Hi 

So I have four columns in the sample data as you see.. 

Ispoc ID, Idea owner Id, Project manager Id, Ichamp Id

I want sum of distinct count of employee id in these four columns filtered on the basis of project completion date, & None of the Id's should be repeated in any of the column.

So in the below Example if I take sum of distinct count of each column it would be 20,

But when you compare values across 4 column few are repeated if we remove repeated then the count would be 13.

So this is the output that I want. 

Owner ID	Ispoc ID	Ichamp ID	Project manager ID
1	6	11	12
2	7	10	10
3	8	7	2
4	9	8	13
5	2	1	2

[Anonymous]

Sure