Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Distinct Count of Values Based on Two Columns

Hi, 

 

I have the following table:

 

 

I'd like to create a third column to tell me the unique calls. All calls at the same time with the same phone number are calculated as one unique call. 

 

The result should be something like this: 

 

 

I tried the following but it didn't quite work out: 

AnotherTable = SUMMARIZE(journal;journal[StartTime];journal[PhoneNumber];"Distinct Count";DISTINCTCOUNT (journal[PhoneNumber]))

 

Has anyone an idea how I should get the required result?

 

Thanks

 

 

Need Help

3 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

    I tested with this formula, it returns the same result table as you provided above.

    AnotherTable = SUMMARIZE(journal;journal[StartTime];journal[PhoneNumber];"Distinct Count";DISTINCTCOUNT (journal[PhoneNumber]))

     

    Based on my assumption, the expected result of "DistinctCount" column should be 7, right?

    AnotherTable =
VAR temptable =
    SUMMARIZE (
        journal,
        journal[StartTime],
        journal[PhoneNumber],
        "Distinct Count", DISTINCTCOUNT ( journal[PhoneNumber] )
    )
RETURN
    SELECTCOLUMNS (
        temptable,
        "StartTime", [StartTime],
        "PhoneNumber", [PhoneNumber],
        "DuniqueCalls", COUNTROWS ( temptable )
    )

     

    Best regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      hi Yuliana Gu, 

      If I run this formula, it gives me the result below. For example at 08:54:58, I have 5 discount counts from the same phone number, I would only like one of these calls from this phone number to be counted at 08:54:58.

       

      If I run the other command, I get the following:

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous,

         

        Please add [StartTime] and [PhoneNumber] fields into table visual and choose "Count(distinct)" to check if the results are correct. The distinct count results should be smaller than total row numer of table 'journal'.

         

        If results are incorrect, please locate to Query Editor, Trim the fields to remove the invisible characters inside text.

         

        Best regards,

        Yuliana Gu