Distinct Count of Two Columns

[Anonymous]

Hi guys

How can I calculate a distinct count of two columns?

Measure:= DISTINCTCOUNT( [EmpID1] ) + DISTINCTCOUNT( [EmpID2] ) won't work because EmpID "123" could be present in both EmpID1 and EmpID2, i.e. it will double count it.

I'm looking for something like: Measure:= DISTINCTCOUNT( UNION( VALUES( [EmpID1] ), VALUES( [EmpID2] ) ) ).

Suggestions?

Thanks,

Simon

[ankitpatira]

@Anonymous What you can do is create calculated column that concatenate values from two columns and then get distinct count from that calculated column.

[Anonymous]

@ankitpatira Thanks for the quick response Ankitpatira.  That's close but not quite what I'm looking for.  Also, my data set is 1.1 billion rows - I cannot afford a calculated column.

Take the below example:

Gold Medals	Silver Medals
USA	Australia
USA	Canada
Germany	Spain
Greece	USA

The distinct count of Gold Medals is 3, Silver medals is 4.  However, I am looking for the distinct count of the population which in this case would be 6.  This can be done in SQL by performing a distinct count of the union of [Gold Medals] and [Silver Medals].  I'm chasing a DAX solution.

Thanks,

Simon

[v-micsh-msft · Accepted solution]

Hi Simon_Nuss,

Currently I don’t think only using measure could achieve this.

In addition to concatenate those two columns, we could create a special column to mark the same value in those two columns.

Then use the two distinct value to minus the sum of the same value column, take use of your example here:

Create the calculated column with the following formula:

Samevalue = if(

                        LOOKUPVALUE( Table1[Gold], Table1[Gold], Table1[Sliver] ) <> BLANK(),

                          1,

                          0)

Modify the count measure with the following:

Measure := DISTINCTCOUNT(Table1[Gold])+DISTINCTCOUNT(Table1[Sliver])-calculate(DISTINCTCOUNT(Table1[Sliver]), Table1[Samevalue]=1)

See the result:

Hope this should be helpful.

Regards

[Anonymous]

@Michel well - I'm seeing Marco Russo this evening so I know what I'll be asking him.  Thanks for your help mate!

[v-micsh-msft]

Hi Simon_Nuss,

Do you need any further assistance on this topic?

Regards

[Anonymous]

No thanks, I actually forgot to ask Marco - oh well!  I'll revisit it in a few weeks and try to think of an elegant solution.