Contains not working when table not related!

[Anonymous]

Hi,

I have two tables(a & b) and both are not linked.

so ideally what i'm trying to do is filter table a when table b has a selection.

I understand this is much easier when its related but the end goal is not what I'm looking for so I want it to work when its not related.

for example in table b lets says I have name of cities and when I select multiple cities I want the table a to be filtered accordingly

I have a measure which creates a concatnated list of selected cities with a | seperator and 

Selected Cities = CONCATENATEX ( VALUES ( b[City] ) , [City] , " | ")

 Now all i am trying to do is create another measure which searches if a string is withing the Selected Cities

Search = CONTAINS(a,a[City],[Selected Cities])

This works when there is just one selection for example lets say the o/p of Selected Cities is "Dubai" then the search does return True next to the Dubai inside a table where as if the "Selected Cities has multiple cities ex "Dubai | Doha" then the search measure returns false no matter what, why is it not searching? 

 
Whats strange is when these tables are related on completely differend column in that case even search value returns true even when multiple cities are selected but I dont want to do that because my main objective is different. 

Please advise what I'm doing wrong.

[amitchandak]

@Anonymous , Not very clear.

1. You should have common dimension and use filter

2. https://docs.microsoft.com/en-us/dax/treatas-function

3. Try a measure like

measure = countrows(filter(Table1, Table1[city] in values(Table2[city])))
measure = countrows(filter(Table1, Table1[city] in allselected(Table2[city])))

[Greg_Deckler]

@Anonymous - Contains matches a value to a column in rows of a table. You probably want SEARCH or FIND. Or, don't concatenate your values, leave them as a table and use CONTAINS.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

@Greg_Deckler @amitchandak 

Thank you for your response please find the below detailed explaination of what i'm trying to acheive.

sample pbix

In the attached pbix I have 3 tables namely

Main

Detailed

City

Main and Detailed are raleted based on column "id" however City is not related to any other table.

All I'm trying to acheive is when I select a city or multiple cities from the slicer created from the City[Cities] example lets say I select "san fransisco" ,"washington" and "france" I want the Main table to have a True or False value so I can use that measure to filter multiple visuals and matrix tables which I have in the report. Please advise how to acheive this?

sample matrix visual BEFORE SELECTION

sample matrix visual AFTER SELECTION

I thought I can use contains to acheive this but its way more complicated than I thought.

Kindly help.

[Icey · Accepted solution]

Hi @Anonymous ,

Change your Measure 3 and Measure 4 like so:

Measure 3 = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Detailed ),
        Detailed[id] = MAX ( Detailed[id] )
            && Detailed[start city] IN VALUES ( City[Cities] )
    )
)

Measure 4 = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Detailed ),
        Detailed[id] = MAX ( Detailed[id] )
            && Detailed[end city] IN VALUES ( City[Cities] )
    )
)

Or, just create a measure like so:

Filter Measure = 
COUNTROWS (
    FILTER (
        ALLSELECTED ( Detailed ),
        Detailed[id] = MAX ( Detailed[id] )
            && (
                Detailed[end city] IN VALUES ( City[Cities] )
                    || Detailed[start city] IN VALUES ( City[Cities] )
            )
    )
)

Best Regards,

Icey

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