String Matching using Filter in Direct Query

[musmanrafiq]

Hello,

I have two tables in SQL DB

The first Table contains Column "Type" with the following values "Amazon, Store, Online Retail"

Type

Amazon

Store

Online Retail

The 2nd Table contains two columns CustomerID and TypeSelected

CustoemrID	TypeSelected
1	some text ,Amazon , Sometext
2	some text ,OnlineRetail, Sometext
3	some text ,Store, Sometext
4	some text ,Amazon ABC , Sometext

How can I create a filter/slicer using the First Table field 'Type' which applies on my 2nd table and only shows specific Type Rows

Example: Amazon is selected from Filter Only first and the Last row will return.

I am using SQL DB with Direct Query Connection

[amitchandak · Accepted solution]

@musmanrafiq , Create a measure like this and use it with columns of table 2

countrows(filter(Table2, search(selectedvalue(Table1[Type]), Table2[TypeSelected],,0) >0), [CustomerID])

[v-shex-msft · Accepted solution]

HI @musmanrafiq,

You can use the following measure formula as a filter on your visual to filter records based on type selections:

Measure = 
VAR currRow =
    SELECTEDVALUE ( T2[TypeSelected] )
VAR result =
    COUNTROWS (
        FILTER ( ALLSELECTED ( T1[Type] ), SEARCH ( [Type], currRow, 1, -1 ) > 0 )
    )
RETURN
    IF ( result > 0, 1, 0 )

Applying a measure filter in Power BI - SQLBI

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

@musmanrafiq , Create a measure like this and use it with columns of table 2

countrows(filter(Table2, search(selectedvalue(Table1[Type]), Table2[TypeSelected],,0) >0), [CustomerID])

[musmanrafiq]

Thanks for the reply,

it says unexpected Parameter CustomerID