Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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:
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?
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.
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.
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:
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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |