98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Power BI training and help » If and Search
If and Search
Resolved · Urgent Priority · Version 2016
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
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Power BI tip:Master DAX (Data Analysis Expressions)DAX is the formula language used in Power BI for creating custom calculations and aggregations. Invest time in learning DAX to unlock the full potential of Power BI. Understand functions like CALCULATE, FILTER, and SUMX, as they are fundamental for creating complex calculations and measures. |