Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DevDelwyn
Frequent Visitor

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
1 ACCEPTED SOLUTION

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.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Sample data would be really helpful. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg, I have added a short data example above.

 

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.

Capture.PNG

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

That is great @v-piga-msft  thank thankyou 🙂

 

I do have a followup query in that I also would like to count just the instances where the Customer Number has repeated on seperate days (excluding the earliest record)

 

i.e from the above data set would return the results as follows

Customer NoMeasure
AA012
AA021
AA030

 

I have tried variations on your previous solution but again am coming up short.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.