if and search

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Power BI training and help » If and Search

If and Search

resolvedResolved · Urgent Priority · Version 2016

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

 

Training courses

 

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
What does 'Resolved' mean?

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.

View all Power BI hints and tips


Server loaded in 0.07 secs.