Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

lookup value and search string

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
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

4 Replies

  • AllisonKennedy's avatar
    AllisonKennedy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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 😞 

       

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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.