Counting Number of Occurrences of a String Over a Dynamic Date Range

[Regal]

Hi all,

I have a column 'CountDistinctSearchedName' which counts the number of times column values [Searched_Name] appears in my date set, like this :

CountDistinctSearchedName = COUNTX ( FILTER ( ScrutinySearchHeader, EARLIER ( ScrutinySearchHeader[Searched_Name] ) = ScrutinySearchHeader[Searched_Name]), ScrutinySearchHeader[Searched_Name])

I wish to compare the counts of those values to counting the same column [Searched_Name] over a given period, each [Searched_Name] having a [Search_Date], so given the following:

Searched_Name	Search_Date
white	01-Dec-18
white	01-Dec-18
white	01-May-19
red	01-Dec-18
red	01-May-19
red	01-May-19
red	01-May-19
blue	01-Dec-18
blue	01-Dec-18
blue	01-May-19
black	01-Dec-18
black	01-Dec-18

I'd like to be able to produce the following:

Searched_Name	Search_Date	Total Count	Count In Last 3 months Based on Search_Date
white	01-Dec-18	3	1
red	01-Dec-18	4	3
blue	01-Dec-18	3	1
black	01-Dec-18	2	0

Help is much appreciated.

Thanks

[v-xicai]

Hi @Regal ,

For your demand, I have two questions showing picture below, especially, which date range is for "the Last 3 month ", because the count values seems not to be calculated by the range date.

Best Regards,

Amy

[Regal]

Thanks for your response @v-xicai 

For Q1 - I don't need the search date in the final table, that's an error in my screenshot, apologies.

For Q2 - I meant the previous 3 months from today. But actually I relaised last night there was a very simple solution to this problem I had overlooked (too long staring at a screen yesterday I think). In the end I just used a table visualisation, added Searched_Name and Count of Searched_Name with a top N filter, and then used the same visual and added a relative date filter on the Search_Date for the last 3 calendar months.

But just out of curiosity anyway, do you know if there is a way to add additional filters into this code below, specifically around date ranges? Wherever I try to put additional filters in I get EARLIER/EARLIEST refers to an earlier row context which doesn’t exist error. 

CountDistinctSearchedName = COUNTX ( FILTER ( ScrutinySearchHeader, EARLIER ( ScrutinySearchHeader[Searched_Name] ) = ScrutinySearchHeader[Searched_Name]), ScrutinySearchHeader[Searched_Name])

[v-xicai · Accepted solution]

Hi @Regal ,

For the error message, EARLIER and EARLIEST are usually used in calculated column. You can go through the link to learn more: https://docs.microsoft.com/zh-cn/dax/earlier-function-dax.

Based on my test creating a calculated column when use this DAX, it work fine. While, the place of EARLIER is usually like this:

CountDistinctSearchedName = COUNTX ( FILTER ( ScrutinySearchHeader, ScrutinySearchHeader[Searched_Name] = EARLIER(ScrutinySearchHeader[Searched_Name])), ScrutinySearchHeader[Searched_Name])

If your need to create CountDistinctSearchedName in measure, you can try this DAX.

CountDistinctSearchedName_measure = CALCULATE(COUNT(ScrutinySearchHeader[Searched_Name]),FILTER(ScrutinySearchHeader, ScrutinySearchHeader[Searched_Name]=MAX(ScrutinySearchHeader[Searched_Name])))

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.