Forum Discussion

DevDelwyn's avatar
DevDelwyn
Frequent Visitor
7 years ago
Solved

distinctcount filter different days

Hi, I am new to DAX and struggling to find a working solution.

 

On a Table 'Call Log' I have an [Customer No] colIumn and [Contact Date] whixh shows all calls received.

 

I am trying to show how many time customers contacted (count of entries in table for Customer No) but only counting 1 instance for each Date. (ie if Customer X contacted twice on Monday and once on Tuesday result should show "2".

 

My latest incorrect result is below but having read through a number of queries on similar topics using GroupBy or SUMMARIZE nothing I have found is producing the correct results

Repeat Contact Count = CALCULATE(DISTINCTCOUNT('Call Log'[Customer No.]),SUMMARIZE('Call Log'[Customer No.],'Call Log'[Contact Date]))

Any help or guidance would be grately received.

 

Example Data

Customer NoContact Date
AA0101/01/2019
AA0201/01/2019
AA0101/01/2019
AA0301/01/2019
AA0102/01/2019
AA0202/01/2019
AA0103/01/2019

 

 

Expected Result

Customer NoCount of Contact
AA013
AA022
AA031
Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi DevDelwyn ,

    Please try the measure below.

    Measure =
VAR t =
    SUMMARIZE ( 'Table1', 'Table1'[Customer No], 'Table1'[Contact Date] )
RETURN
    CALCULATE ( COUNTROWS ( t ), ALLEXCEPT ( Table1, Table1[Customer No] ) )

    Here is the output.

    Best  Regards,

    Cherry

     

4 Replies