Measure to find every occurrence of a string, except the first

[Anonymous]

Hi folks, I'm hoping someone can assist.

I have a table of data (example below), that contains some Reference Numbers, as well as audit log entries relating to each RefNum, and datestamps for when those audit log entries occurred. Not that the data may appear in any order:

RefNum	ActivityType	DateCreated	Analyst
1	Opened	01/09/2019 10:00am	Jim
1	Comment	01/09/2019 10:01am	Jim
1	Search	01/09/2019 10:02am	Jim
1	Search	01/09/2019 10:03am	Barry
2	Opened	01/09/2019 10:04am	Barry
2	Comment	01/09/2019 10:05am	Barry
2	Comment	01/09/2019 10:06am	Barry
2	Search	01/09/2019 10:07am	Jim
3	Opened	01/09/2019 10:08am	Jane
3	Comment	01/09/2019 10:09am	Sally
3	Comment	01/09/2019 10:10am	Jane
3	Comment	01/09/2019 10:11am	Jane
4	Opened	01/09/2019 10:12am	Jane
4	Search	01/09/2019 10:13am	Sally
4	Comment	01/09/2019 10:14am	Jane
4	Search	01/09/2019 10:15am	Jane

I want to be able to flag ONLY the first occurrence in each RefNum of "Search", based on the earliest DateCreated. A kind user put together the measure below for me:

Flag = 
IF (
    (
        'Table'[DateCreated]
            = CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] )
            )
    )
        && (
            CALCULATE (
                MIN ( 'Table'[DateCreated] ),
                ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] )
            )
                = CALCULATE (
                    MIN ( 'Table'[DateCreated] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[Analyst], 'Table'[RefNum] ),
                        'Table'[ActivityType] = "Search"
                    )
                )
        ),
    "Not Count"
)

Which gives me the results below. I'm actually fine with it giving me 'Not Count', as I can use Power Query to fill in the blanks with a 'Count' string and give me the result I need (this is also why I've set the title of this request as such). But it's not currently flagging the right lines with 'Not Count', which is what I need help fixing. The 'X' represent correct incorrect flags of 'Not Count', and the ticks represent fields where 'Not Count' should appear:

Any help would really be appreciated!

[az38]

@Anonymous 

how are you going to "use Power Query to fill in the blanks with a 'Count' string" if your column/measure is created in DAX-mode? its impossible. PowerQuery is being used only for transform data source, before implement business logic

do not hesitate to give a kudo to useful posts and mark solutions as solution
Linkedin

[Anonymous]

Good point @az38  - I would likely have needed to create some sort of calculated column instead (and I would probably have wasted 30 mins of my life trying to figure out why I couldn't do it in Power Query, if you hadn't replied) 🙂

[JarroVGIT · Accepted solution]

Well, based on your example table, I have created the following calculated column:

FirstSearch = 
    VAR currentActivityType = [ActivityType]
    VAR currentRefNum = [RefNum]
    VAR rank1 = RANKX(FILTER('Table', 'Table'[RefNum] = currentRefNum && 'Table'[ActivityType] = currentActivityType), 'Table'[DateCreated],,ASC,Dense)

    RETURN
        IF((rank1=1 && currentActivityType = "Search"), TRUE, FALSE)

What is happening here is the following. For each row this is evaluated, so variable currentActivityType and currentRefNum are just referencing other columns of the row we are adding a new column to. The rank1 (rank is a reserved word) variable calculates what n-th time this row is based on refnum, sort by date for that activity type. The IF-statement checks if the rank=1 (it is the first time this type of activity is occured in the current refNum) and if the activitytype is Search. 

This results in the desired output:

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[Anonymous]

Thanks for the solution and the explanation @JarroVGIT , I really appreciate it 🙂

[JarroVGIT]

No sweat, just don't forget to mark it as the solution so other with similar questions can find it easily 🙂

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Kudo's are welcome 🙂

[TomMartens]

Hey @Anonymous 

maybe you want to try this DAX creating a Calculated Column:_

is 1st search = 
var firstSearch = CALCULATE(MIN('Table2'[DateCreated]) , ALLEXCEPT('Table2' , Table2[RefNum]) , 'Table2'[ActivityType] = "Search")
var _refnum = 'Table2'[RefNum]
return
if(AND('Table2'[DateCreated] = firstSearch , 'Table2'[RefNum] = _refnum) , "yes" , "no") 

Hopefully this provides what you are looking for.

Regards,

Tom 

[Anonymous]

Thanks very much @TomMartens , I really appreciate you taking the time to provide a respose here 🙂