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
cedmiston
Frequent Visitor

Change a value based on a text search

I have three columns that all contain the same text fields (in this case names of all our projects). Anytime this project name is entered in any of the three columns I want it to show on my table, so I wrote a formula to concantanate them and used this as a text search filter and it works great.

 

However, if what the person is searching for shows up in only ONE of the three columns can I change another column's value (in this case my project cost) from a negative to a positive number?

 

So, based on a TEXT Search box can I write a measure? Not even sure if this is possible. Thanks.

1 ACCEPTED SOLUTION

Hi @cedmiston 

 

If you'd like to filter value in multiple columns please refer to: 

https://community.powerbi.com/t5/Desktop/How-to-filter-on-multiple-columns/td-p/380232 

http://www.dynamicinfo.nl/filter-on-multiple-columns-with-power-bi/ 

 

If you'd like to set the condition: if text filter = column A, Then column B is a negative number, if not is positive, please refer to below steps:

1. Create a new table with one column Filter, and set it as the text filter. Don't manage the relationship with the fact table

2. Add below measure:

Measure = IF(SELECTEDVALUE(TextFilter[Filter])=MAX('Table'[ColumnA]),ABS(MAX('Table'[ColumnB]))*-1,ABS(MAX('Table'[ColumnB])))

3. Final results:

4.PNG

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

7 REPLIES 7
JarroVGIT
Resident Rockstar
Resident Rockstar

Could you share a sample of your data (and with that, your datastructure)? I have a hard time understanding what you are trying to achieve. How are your users using the reports?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Yes, let me know if this makes sense.

 

Searching for project "Board Design" - anytime it's listed in "project name", "Time Override", or "Activity Override" it is on the report.

However, if it is in the Time override column, then the Acutal Cost needs to be a positive number, not a negative number. (AKA the yellow highlighted colum needs to be a positive number when I search for "Board Design" in my search text box.

 

The thought is: If the "override" columns are blank = amount is positive.

If there is data in either "override" column, the amount is negative.

However, if the text I searched for aka "Board Design" is in the override column, that number changes from a negative to a positive.

So, the total project cost should equal: $793 instead of the two totals shown.

Annotation 2019-12-10 151408.jpgAnnotation 2019-12-10 151316.jpg

Hi @cedmiston 

 

You might consider creating pbix file that will contain some sample data, upload the pbix to onedrive or dropbox and share the link to the file. that we can repro it easier.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

I will try to re-create as a .pbx file. Unfortunately I can't share my original report as the data is propriatary and confidential.

Can't upload a pbx file but basically I am trying to create the following DAX formula:

 

= IF (text from text filter =(is found/matches) ColumnA, then Column B is a negative number, if not is a positive number).

 

Does that make sense? Is that even doable? I haven't found how you would include a text search result into a formula.

Hi @cedmiston 

 

If you'd like to filter value in multiple columns please refer to: 

https://community.powerbi.com/t5/Desktop/How-to-filter-on-multiple-columns/td-p/380232 

http://www.dynamicinfo.nl/filter-on-multiple-columns-with-power-bi/ 

 

If you'd like to set the condition: if text filter = column A, Then column B is a negative number, if not is positive, please refer to below steps:

1. Create a new table with one column Filter, and set it as the text filter. Don't manage the relationship with the fact table

2. Add below measure:

Measure = IF(SELECTEDVALUE(TextFilter[Filter])=MAX('Table'[ColumnA]),ABS(MAX('Table'[ColumnB]))*-1,ABS(MAX('Table'[ColumnB])))

3. Final results:

4.PNG

 

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Thank you so much that worked!

 

I did hit one error in that the text has to match exactly for those using this in the future. So entering "a" or "A" will work, but entering AAAA will not.

 

Thanks for the help.

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.