Multiple condition text search criteria

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I have a search criteria table that I want to use to do a search for multiple conditions against in my main table

Using the below formula works well for a single text search criteria

=FIRSTNONBLANK(FILTER(VALUES(

'SearchC'[Search Criteria 1])

,SEARCH('SearchC'[Search Criteria 1],'Data3-Work Order List'[Description],1,0)),1)

However Im stuck when trying to add another optional search criteria in another column of the SearchC Table ie: Search Criteria 2 as this formula doesn’t work when there are blank rows in the table

How would I be able to add ie: Looks for text containing Search Criteria 1 & Search Criteria 2?

Are you able to help point me in the right direction?

Thanks

Richard

2

I think you want to be using the LOOKUPVALUE formula for this.

This function works similar to vlookup in excel and allows you to input multiple search criteria.

Try working through the syntax for this one. I think you’ll find it’s quite easy to use.

I use it in this example here (around 5 min mark)

3

Hi Sam. Look up value only operates the same way as vlookup in excel though. Unless there is functionality I am not aware of?

What I am after is a search text function ie: Look for the keyword “match” in long text

Yes I know I can do this via the query editor but I am looking at having user inputs which limits this to DAX only unfortunately

4

Hi
If I understand correctly you want to search keywords within the dataset of the model. So from the report level a user can search “DAX” in a columns that has DAX and it filters from the report level everything for DAX.
I have done a similar thing - but here I used a custom visuals - Text Filters.

My main table below, and i’m doing multiple searches based on the top section criteria. Based on this the table will filter down.

Hope this helps.

image
image.jpg1471×832 505 KB

5

Hi

I am aware of text filters Thanks but what I am looking for is a search for multiple criteria using dax in the same field column ie: POWER BI AND DAX for example. User input is being passed through from poweraps and isn’t the issue.

Any ideas?

Thanks

6

Possible to add an example file with the scenario to understand this a bit more?

Just want to see the example and then run a couple of tests.

Chrs

7

Hi

Many Thanks for having a look Sam

See attached example - I want to have a dax formula that looks up Search Criteria 1 AND Criteria 2 for example

Leak and Tap in a work description field and return that the work done relates to a leaking tap etc

What ive done fails when the search lookup table is expanded to plumbing and electrical and there are blank cells however - see Sample Pbix 2

Sure there must be a simple way of doing this in DAX that im not aware of !

Thanks

Sample PBIX.pbix (44.6 KB)
Sample PBIX 2 - failed search.pbix (48.5 KB)

8

Ok played around on this for quite a while, as was bit tougher than I thought.

I’ve used this formula and it’s got it working I think

Search Column 2 = 
VAR SearchColumn = FILTER( DISTINCT( 'Lookup table'[AND Search Criteria 2] ), 'Lookup table'[AND Search Criteria 2] <> BLANK() )

RETURN
CALCULATE( SELECTEDVALUE( 'Lookup table'[AND Search Criteria 2], BLANK() ),
    FILTER( SearchColumn,
        SEARCH( 'Lookup table'[AND Search Criteria 2], 'Table1 - Text Table'[Work Description], 1, 0 ) ) )

The blanks were the issue I realized, so I got rid of this in the variable.

Uploading…

Let me know if this was what you were looking for.

I also change the first formula as well. I think this is better personally

Search Column 1 = 
CALCULATE( SELECTEDVALUE( 'Lookup table'[Search Criteria 1], BLANK() ),
    FILTER( VALUES( 'Lookup table'[Search Criteria 1] ),
        SEARCH( 'Lookup table'[Search Criteria 1], 'Table1 - Text Table'[Work Description], 1, 0 ) ) )
9

Hi Sam

Many thanks for this again! Was stuck on this problem for a while …

I can see what you have done here and its a clever use of SELECTEDVALUE wrapped up in filters

Which seems to be the way with power BI when you want to do something out of the box as there are many ways to get a result

Kind regards

Richard

10

Yep that’s right. Nice one.