lookup value and search string

[Anonymous]

Hi all ... 

I have aged a decade trying to figure out a way around this 

My 'Key Search' formula works well for another report I built, but I can't figure out how to add an extra criteria to it: 

Key Search = var result = CONCATENATEX('Report Key',IF(SEARCH(FIRSTNONBLANK('Report Key'[SearchString],1),PROJEMPTRANS[CRSEXTCOMMENT],,1111)<> 1111,'Report Key'[Work Type],"")) return if(result <> blank(), result, "n/a")

I need the formula to search the column in my data table 'CRSEXTCOMMENT' and match the SearchString to the comment - THEN - I need it to look at the 'WRKCATEGORY' of that row, match it to the 'Category' column to return the correct 'Work Type' 

i.e - 

If the CRSEXTCOMMENT contains  'Project Managment' and the Wrk Category assigned is 'Consulting hours' than return 'Project Management' 

If the CRSEXTCOMMENT contains 'Project Managment' and the Wrk Category assigned is 'Management' than return 'Non chargeable Project Management' 

and so on... 

Reason for this is that I need to be able to report on chargeable and non chargeable project management time booked to a project - also people may write in more information than just the key string text so its a failsafe. 

If anyone could help me out or tell me if I am over egging it I would really appreciate it 🙂 

Be blessed 

Anne

[AllisonKennedy]

@Anonymous  It looks like you already have the results column in the screenshot of your table? What is it that you are missing or what's not working? Also see if the CONTAINSSTRING function might help here?

[Anonymous]

Im half way there, problem is as the key word search is not unique I get this:

I need it to say either - 'non chargeable project management' or 'project management' not both

As the 'wrk category' for this line is 'Consulting hours' it should return 'Project Management' as those words in that category relate to work type 'project management' in my report key table. 

😞I'm trying to see what I can do with 'containsstring' but im just getting errored out 😞 

[AllisonKennedy]

@Anonymous  The conditions need to be unique. Why do you know that it's Project Management for that row? You just need to add that as an additional condition. You can use 

&&

to combine multiple conditions into one statement/filter expression.

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can create a measure to calculate the desired result.

Like this:

Measure =
IF (
    SEARCH (
        SELECTEDVALUE ( 'Report Key'[SearchString] ),
        PROJEMPTRANS[CRSEXTCOMMENT],
        ,
        0
    ) > 0
        && SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Consulting hours",
    SELECTEDVALUE ( 'Report Key'[SearchString] ),
    IF (
        SEARCH (
            SELECTEDVALUE ( 'Report Key'[SearchString] ),
            PROJEMPTRANS[CRSEXTCOMMENT],
            ,
            0
        ) > 0
            && SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Management",
        "Non chargeable " & SELECTEDVALUE ( 'Report Key'[SearchString] ),
        "n/a"
    )
)

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

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