The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi all ...
I have aged a decade trying to figure out a way around this
My 'Key Search' formula works well for another report I built, but I can't figure out how to add an extra criteria to it:
Solved! Go to Solution.
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description,I think you can create a measure to calculate the desired result.
Like this:
Measure =
IF (
SEARCH (
SELECTEDVALUE ( 'Report Key'[SearchString] ),
PROJEMPTRANS[CRSEXTCOMMENT],
,
0
) > 0
&& SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Consulting hours",
SELECTEDVALUE ( 'Report Key'[SearchString] ),
IF (
SEARCH (
SELECTEDVALUE ( 'Report Key'[SearchString] ),
PROJEMPTRANS[CRSEXTCOMMENT],
,
0
) > 0
&& SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Management",
"Non chargeable " & SELECTEDVALUE ( 'Report Key'[SearchString] ),
"n/a"
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous It looks like you already have the results column in the screenshot of your table? What is it that you are missing or what's not working? Also see if the CONTAINSSTRING function might help here?
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Im half way there, problem is as the key word search is not unique I get this:
I need it to say either - 'non chargeable project management' or 'project management' not both
As the 'wrk category' for this line is 'Consulting hours' it should return 'Project Management' as those words in that category relate to work type 'project management' in my report key table.
😞I'm trying to see what I can do with 'containsstring' but im just getting errored out 😞
@Anonymous The conditions need to be unique. Why do you know that it's Project Management for that row? You just need to add that as an additional condition. You can use
&&
to combine multiple conditions into one statement/filter expression.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Hi, @Anonymous
It’s my pleasure to answer for you.
According to your description,I think you can create a measure to calculate the desired result.
Like this:
Measure =
IF (
SEARCH (
SELECTEDVALUE ( 'Report Key'[SearchString] ),
PROJEMPTRANS[CRSEXTCOMMENT],
,
0
) > 0
&& SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Consulting hours",
SELECTEDVALUE ( 'Report Key'[SearchString] ),
IF (
SEARCH (
SELECTEDVALUE ( 'Report Key'[SearchString] ),
PROJEMPTRANS[CRSEXTCOMMENT],
,
0
) > 0
&& SELECTEDVALUE ( 'Report Key'[Work Type] ) = "Management",
"Non chargeable " & SELECTEDVALUE ( 'Report Key'[SearchString] ),
"n/a"
)
)
Here is my sample .pbix file.Hope it helps.
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.