Help with Multiple Text Filters

[MuppetyMe]

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. 

[MuppetyMe · Accepted 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

[Anonymous]

@MuppetyMe 

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.

[MuppetyMe]

Sorry friend, novice PBI user 🙂

[ERD]

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! 

[MuppetyMe]

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 🙂

[MuppetyMe]

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. 

[ERD]

@MuppetyMe , 

Try to use "MAX" instead of "SELECTEDVALUE"

[amitchandak]

@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())

[MuppetyMe]

@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.

[Anonymous]

@MuppetyMe 

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

[MuppetyMe]

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.