Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Distinct Count of Two Columns

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...
Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    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's avatar
Anonymous
Not applicable
9 years ago

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 MedalsSilver Medals
USAAustralia
USACanada
GermanySpain
GreeceUSA

 

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's avatar
v-micsh-msft
Microsoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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's avatar
      v-micsh-msft
      Microsoft Employee
      9 years ago

      Hi Simon_Nuss,

      Do you need any further assistance on this topic?

      Regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        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.