Forum Discussion
Quarterly Report- Select First Entry
I need to create a quarterly report that scans a SP list and counts an contact as new if it is the first entry in a designated time period.
For Example: If I create a slicer with a begin and end date, I want to only count those entries that have had their first entry within that time period.
For example, If Geddy Rush has mulitple entries, I only want to count his first within the alotted time period.
My organization has to document how many new contacts for each quarter. Thx so much!
Sample Data:
TitleLogStudentNameLogContactDateLogContactType
| 300959 | ADAMS, JOHN | 4/11/2024 | Phone |
| 300959 | ADAMS, JOHN | 2/21/2024 | Phone |
| 300959 | ADAMS, JOHN | 2/20/2024 | Phone |
| 280436 | ALEXANDER, BILLY BOB | 4/15/2024 | Student Conference |
| 290215 | ALLEN, TIM | 2/21/2024 | |
| 290215 | ALLEN, TIM | 2/7/2024 | Student Conference |
| 290197 | ALLEN, TOM | 4/22/2024 | Student Conference |
| 290197 | ALLEN, TOM | 3/11/2024 | Student Conference |
| 290197 | ALLEN, TOM | 3/11/2024 | Student Conference |
| 290197 | ALLEN, TOM | 3/7/2024 | Student Conference |
| 290197 | ALLEN, TOM | 2/8/2024 | Student Conference |
| 290197 | ALLEN, TOM | 1/19/2024 | Student Conference |
| 301086 | ANDERSON, LEXUS CAR | 1/19/2024 | Student Conference |
Hi slhangen
You can add a calculated column, which will rank the entries by date.Count_times = RANKX(FILTER('Table', 'Table'[Id]= EARLIER('Table'[Id])),'Table'[date],,ASC,DENSE)and then use a simple dax for measure with filtering first entry only
First entries = CALCULATE(DISTINCTCOUNT('Table'[Id]),'Table'[Count_times]=1)pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
5 Replies
Hi slhangen
You can add a calculated column, which will rank the entries by date.Count_times = RANKX(FILTER('Table', 'Table'[Id]= EARLIER('Table'[Id])),'Table'[date],,ASC,DENSE)and then use a simple dax for measure with filtering first entry only
First entries = CALCULATE(DISTINCTCOUNT('Table'[Id]),'Table'[Count_times]=1)pbix is attached
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
This was amazing! Works perfectly. Can I ask you to briefly walk me through what it does (1-2 sentences) as I am trying to expand my knowledge. Especially the DENSE function, never seen that one.
Thx again!!!
Hi slhangen
The
RANKXfunction operates by assigning ranks to rows within a group based on a specified expression. In this instance, the expression utilized is thedatecolumn, ensuring that rows within eachIdgroup are ranked according to their corresponding dates.To effectively group rows based on their
Idvalues, theFILTERfunction is employed. This function creates a temporary table that exclusively contains the rows corresponding to the currentIdvalue. By leveraging theEARLIERfunction, theIdvalue from the preceding row is retrieved, ensuring that the ranking process is confined within eachIdgroup.The
ASCparameter plays a crucial role in determining the ranking order. It specifies that the ranking should be performed in ascending order, implying that earlier dates will receive lower ranks.To address situations where duplicate values exist, the
DENSEparameter is utilized. It ensures that ranks are assigned sequentially, without any gaps, even when duplicate values are encountered. For instance, if three rows share the same date, they will be assigned ranks of 1, 2, and 3, rather than 1, 1, and 4.More information about Rankx is here :
https://learn.microsoft.com/en-us/dax/rankx-function-daxOher way to give a kind of index to every row inside the id is to work with PQ , take a look at the linked tutorial :
https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
