Elsa has attended:
Access Intermediate course
Power BI Modelling, Visualisation and Publishing course
If and Search
Hi
I have a list of task names as shown below:
OCLC (P)
OCLC - 123 (P)
OCLC - 234 (P)
OCLC - 456 (P)
OCLC (M)
I would like to group the above (P) projects together as 'OCLC (P)' in a new column but exclude the task 'OCLC (M).
I have put in a DAX below:
OCLC = if(search("OCLC",summ_task[task_name],1,0),"OCLC (P)", if(search("OCLC (M)", summ_task[task_name],1,0), "OCLC (M)", "Other"))
However, the task 'OCLC (M)' is still included and it is shown as 'OCLC (P)' in the new column.
Please how should I adjust the DAX that only have all tasks contain 'OCLC' as 'OCLC (P)' excluding the 'OCLC (M)'?
Thanks
Elsa
RE: If and Search
Hi Elsa
Thank you for your question.
In order to answer it I need some more information from you. Do any of the other task names not listed contain the letter P? If not then your job will be easier as you can just replace the "OCLC" with "P" (in the first IF(SEARCH part of your DAX) and that should work.
If your other task names do contain P, the DAX will need to be more complex and I can help you then.
Please let me know how you get on with your problem
Kind regards
Martin
RE: If and Search
Hi Martin
I do have other tasks with (P), therefore I am not able to replace OCLC with P.
Thanks
Elsa
RE: If and Search
Hi Elsa,
Thanks for your reply.
So it looks like you need to build into your existing DAX formula an AND function where both OCLC and P need to be included as part of the test in the IF function. Your DAX should look like this:
OCLC=IF(AND(SEARCH("OCLC",summ_task[task_name],1,0),SEARCH("P",
summ_task[task_name],1,0)),"OCLC (P)", IF(SEARCH("OCLC (M)", summ_task[task_name],1,0), "OCLC (M)", "Other"))
Let me know how you get on
Kind regards
Martin
RE: If and Search
It works perfectly! Thanks Martin! :)
RE: If and Search
Hi Elsa,
You're welcome.
Kind regards
Martin
