Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Returning date from [TaskFinishDate] column if text in [TaskName] column contains specified text

I am using PBI to pull data from Project Online in order to create a table that accomplishest the following:

  1. Looks at all projects and their tasks in Project Online and returns the finish date of the task when a task name contains a certain text.
  2. If it the criteria in #1 is not met then a value of "N/A" is populated 
    • Example: If task name is "Complete testing for Asia" and the finish date is 6/12/2019. I want PBI to look for task names that contain "Complete testing" and then populate the finish date. I've been researching/trying different variations of IF statements with wildcard characters but thus I have had to enter the entire task name to return the finish date.

Below is the formula I've been trying:

  1. Complete Testing = IF([Task Name] = "Complete testing for Asia",[Task Finish Date],0)
    • No wildcard characters - This has worked but the test must be exact and if the criteria is not met it returns a value of 12/30/99
  2. Complete Testing = IF([Task Name] = "*Complete testing*",[Task Finish Date],0)
    • Wildcard characters - This does not work and returns a value of 12/30/99

Here is an example of what the end results I am after:

PWA Global Tracker MTD Example.jpg

 

2 REPLIES 2
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

For string comparison in dax, you can refer to following blog to know more about these:

From SQL to DAX: String Comparison

In addition, I think search/find function should more suitable for your requirement.

Complete Testing = IF( SEARCH( "Complete testing", [Task Name], 1, 0 ) > 0,[Task Finish Date],0)

If above not help, please share some sample data with expected result for test.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Hi @v-shex-msft ,

 

Thank you very much for sharing this! I will check out the blog post as well as try your proposed function and proviode feedback on my results.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.