Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Contains not working when table not related!

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 bu...
Need Help
  • Icey's avatar
    Icey
    6 years ago

    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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    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.