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 EmpID2, i.e. it will double count it.

 

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

 

Suggestions?

 

Thanks,

Simon

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

     

     

6 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    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'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