Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Needing assistance with an expression to filter multiple text options. For example:
Starts with Text A -or-
Starts with Text B -or-
Starts with Text C -or-
Starts with Text D
Is this even possible? Any suggestions? There are too many variables in the data source to use basic filtering as all of the above have additional words in the titles.
Solved! Go to Solution.
4/9/21 Update: SOLVED!
Received a brilliant suggestion that resolved my issue: "you should be able to use the filters pane. By default it only shows two options but you can first select 4 random [data points] from the Basic filtering and then switch to Advanced to get more filter criteria."
https://community.powerbi.com/t5/Service/Advanced-Filter/td-p/3834
4/9/21 Update: SOLVED!
Received a brilliant suggestion that resolved my issue: "you should be able to use the filters pane. By default it only shows two options but you can first select 4 random [data points] from the Basic filtering and then switch to Advanced to get more filter criteria."
https://community.powerbi.com/t5/Service/Advanced-Filter/td-p/3834
It would really help if you could put parentheses in the right places. Currently, one doesn't know if OR binds stronger than AND or the other way round.
Sorry friend, novice PBI user 🙂
Hello @MuppetyMe ,
If the text starts from A/B/C/D it already cannot start with E/F, so the second part of your expression is not clear to me.
In case of measure:
FirstLettersCheck =
IF(LEFT(SELECTEDVALUE(T[Column]),1) IN {"A","B","C", "D"},1,0)
Did I answer your question? Mark my post as a solution!
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
ALSO @ERD, you are correct in your logic that if the text starts from A/B/C/D it already cannot start with E/F -- so thank you for that 🙂
Hello @ERD, thank you for the solution. Unfortunately this method does not work either... according to MS articles, "SELECTEDVALUE" does not work if the data source is a direct query. I'm frustrated because it feels like I'm super close to a solution with this suggestion, but it doesn't work.
Try to use "MAX" instead of "SELECTEDVALUE"
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
@MuppetyMe , you can create a new column like
if( left([Column],1) in {"A","B","C", "D"} && not(left([Column],1) in {"E","F"} ) , true(), false())
@amitchandak, thank you for the solution. Unfortunately the data admins have the source locked down in a manner that I'm unable to create columns (this is an SSAS cube). To the best of my knowledge I'm limited to measures and basic/advanced filters.
What about composite models where you can mix data from remote sources and local data? Would that not solve your problem? If you don't know what composite models are... Use composite models in Power BI Desktop - Power BI | Microsoft Docs
Hi @Anonymous, thank you for the response. I believe your solution would be appropriate if I were able to mix data sources, but currently I am not able to do so due to the way the admins have the SSAS cube established + the fact I'm using direct query.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |