Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 No | Contact Date |
AA01 | 01/01/2019 |
AA02 | 01/01/2019 |
AA01 | 01/01/2019 |
AA03 | 01/01/2019 |
AA01 | 02/01/2019 |
AA02 | 02/01/2019 |
AA01 | 03/01/2019 |
Expected Result
Customer No | Count of Contact |
AA01 | 3 |
AA02 | 2 |
AA03 | 1 |
Solved! Go to 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.
Best Regards,
Cherry
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
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.
Best Regards,
Cherry
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 No | Measure |
AA01 | 2 |
AA02 | 1 |
AA03 | 0 |
I have tried variations on your previous solution but again am coming up short.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |