Forum Discussion

dparkinson's avatar
dparkinson
Icon for Advocate I rankAdvocate I
8 years ago
Solved

Filtering a table based on values from an unrelated table

I have a table with a column that has multiple values in it.  I'd like to create a slicer from another (lookup) table which filters the results from the first table based on the items selected.   T...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi dparkinson,

     

    Based on my test, you should be able to use the formula below to create a new measure first, then use it as visual level filter(Measure is greater than 0) to get the expected result in your scenario. :smileyhappy:

    Measure = 
COUNTROWS (
    FILTER (
        TechCompanies,
        SEARCH ( TechCompanies[techcode], MAX ( Things[Thing Companies] ),, 0 ) > 0
    )
)

     

    Here is the sample pbix file for your reference. :smileyhappy:

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi dparkinson,

 

Based on my test, you should be able to use the formula below to create a new measure first, then use it as visual level filter(Measure is greater than 0) to get the expected result in your scenario. :smileyhappy:

Measure = 
COUNTROWS (
    FILTER (
        TechCompanies,
        SEARCH ( TechCompanies[techcode], MAX ( Things[Thing Companies] ),, 0 ) > 0
    )
)

 

Here is the sample pbix file for your reference. :smileyhappy:

 

Regards

dparkinson's avatar
dparkinson
Icon for Advocate I rankAdvocate I
8 years ago

That's great.  Thanks for the reply.  Seems to work great -- I'll give it a go on my proper dataset.

 

I think I ideally need to extract that field out to another table somehow and give it a many-to-many relationship so that I can crossfilter more easily etc.  I'll maybe add another question for that.

 

Thanks once again.

D.