Distinct Count of Values Based on Two Columns

[Anonymous]

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

[v-yulgu-msft]

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]

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]

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