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 ...

Need Help

Ritaf1983
2 years ago
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.

first entries.pbix21 KB

Ritaf1983

Super User

2 years ago

Hi slhangen

The RANKX function operates by assigning ranks to rows within a group based on a specified expression. In this instance, the expression utilized is the date column, ensuring that rows within each Id group are ranked according to their corresponding dates.

To effectively group rows based on their Id values, the FILTER function is employed. This function creates a temporary table that exclusively contains the rows corresponding to the current Id value. By leveraging the EARLIER function, the Id value from the preceding row is retrieved, ensuring that the ranking process is confined within each Id group.

The ASC parameter 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 DENSE parameter 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-dax

Oher 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.