Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
hoggwildd
Regular Visitor

Help With finding the earliest Date

‎03-21-2017 10:00 AM

I have a database that I need to be able to count only the first time a row of data appears. Below is part of the database:

 

DateCustomer IDApt setConfirmedissuedsatResultSale Amount
2/27/2017 5:18:48 PM3975001100ROC             
2/28/2017 9:03:05 PM39750011101Leg            
2/27/2017 6:04:14 PM3975011100CTC             
2/27/2017 5:48:22 PM39750211113 
3/6/2017 7:05:05 PM3975021100ROC             
3/8/2017 2:39:46 PM3975021100Unissued       
3/9/2017 10:08:13 AM39750211113 
2/27/2017 12:57:33 PM3975031000ROC             
3/2/2017 4:09:14 PM3975031100CNC             
2/27/2017 6:00:03 PM3975041110CTC             
3/14/2017 5:14:40 PM3975041000Verif           
2/27/2017 6:15:12 PM3975051100CTC             
2/27/2017 6:01:54 PM39750610101Leg            
2/27/2017 1:07:50 PM3975071011Sale           21170.6
3/6/2017 3:57:19 PM3975081111Sale           13387
3/7/2017 12:17:35 PM3975091110NC              
3/8/2017 9:38:57 AM39750911113 
3/18/2017 12:12:22 PM3975091100CTC             
2/27/2017 5:55:42 PM3975101000ROC             
3/6/2017 2:24:53 PM3975101100COC             
2/27/2017 2:21:20 PM3975111100ROC             
3/6/2017 2:10:11 PM3975111111Sale           12887
3/9/2017 4:11:31 PM39751111113 
2/27/2017 1:26:06 PM39751211101Leg            
2/27/2017 1:27:45 PM3975131100ROC             
3/1/2017 5:22:00 PM3975131110NH              
2/27/2017 2:02:50 PM3975141100ROC             
3/1/2017 1:42:26 PM3975141100COC             
2/28/2017 8:01:00 AM39751511115 

 

So, if I want to know how many customer's (customer ID) contacted us in a month I would not want to count everytime we talked to them, only the first time. I.E. customer ID 397500 we spoke to on 2/27 and again on 2/28. Since it is the same customer I would only want to count it one time, for the 27th. Any help, suggestions or ideas are greatly appreciated!

 

HW

Labels:
Message 1 of 14
70,290 Views
1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion
In response to chbraun

‎03-23-2017 08:26 PM

@hoggwildd

If you don't want to use DAX - you can get the same result in the Query Editor using Group By

1) Duplicate your Table

2) then Group By - Customer ID and the new Column "First Contact" you are creating based on the MIN date for each Customer ID

3) Close & Apply

4) Create a Matrix - drag First Contact to the Rows and Customer ID to the Values

(change to Distinct -although the values are already distinct because we did the Group BY)

Follow the picture below...

Query Editor - Group By.gif

 

OPTION 2

You can actually achieve the same result with a simple DAX Column in your current Table

First Contact Column = CALCULATE ( FIRSTNONBLANK('Table'[Date],1), ALLEXCEPT('Table', 'Table'[Customer ID]) )

Then Create a Matrix HOWEVER

1) use the First Contact Column in the Rows (keep only Year and Month from the Hierarchy)

2) drag First Contact Column again but this time to the Values

AND this time you have to change the default earliest to distinct count

Query Editor - Group By ALT.gif

 

Hope this helps! Smiley Happy

Let me know if you have any questions!

 

View solution in original post

Message 10 of 14
79,169 Views
13 REPLIES 13

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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

View All