SQL Like equivalent filter

[fabiomanniti]

If I do my query on the DB I have something like this:

SELECT *
FROM fact.rent f
LEFT JOIN dim.rent d ON f.id_tk = d._tk
LEFT JOIN dim.users du ON du._tk = d.user_id_tk
WHERE 1=1
AND f.d_date_tk = get_last_msnap_date()
AND du.promoter_path LIKE '%9d732d5c-65d9-4937-9224-01c17f9e7d28%'
;

that means that it gets all entries which contains my string into the "promoter_path" field.

I want to do something similar on Power BI.

I would like a filter with all user names, and when I select a user, it should get the corresponding Id and match with all rows which contains that id within the field "promoter_path".

So, in other words, is it possible to create a filter on Power BI which is not "equal to" but "Like to"?

[Anonymous]

Hi @fabiomanniti ,

You could use the CONTAINSSTRING function to filter the text within "9d732d5c-65d9-4937-9224-01c17f9e7d28".

Measure = IF(CONTAINSSTRING(MAX('Table'[promoter_path]),"9d732d5c-65d9-4937-9224-01c17f9e7d28"),1)

The screenshot above is the result returned by the measure, you can use the measure as a visual-level filter instead of putting the measure into the visual.

Drag the measure into the visual-level filters, set show items when the value is 1.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[fabiomanniti]

The problem with this method is this works as long as the "id" I want to filter is always the same.

So I could manually insert the id into the formula.

What I want is a dynamical table so that I can filter a new user, with its own id and the table content changes.

So let's say that I have two tables:

User: user_id

Sales: sale_id, user_id

So I can create a relationship between user and sales through user_id; now, anytime I select a user, I can see all their sales.

I want something similar but, instead of a direct relationship like this I want:

user: user_id

sales: sale_id, something_with_user_id_within_it

The problem is if I create a table, the tables in Power BI are always static.

[tackytechtom]

Hi @fabiomanniti ,

I am sure you can find you are looking for in this blog post here:

From SQL to DAX: String Comparison - SQLBI

Let me know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[fabiomanniti]

@tackytechtom 

Actually, not really.

I have tried something like this:

I created a new table then 

Table =
VAR include_text = SELECTEDVALUE(users[id])
return
CALCULATETABLE(
users,
CONTAINSSTRING(users[path], include_text)
)

But it doesn't seem to work.

I don't see any changing by changing the selection