Forum Discussion

Patrick1985's avatar
Patrick1985
Frequent Visitor
8 years ago

Count If contains text

Hi      I have data source which tags organizations against different aircraft types. Where an Organization covers a number of types it stores this info as a single text string ie AirseekerChinookB...
Need Help
v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Patrick1985,

 

Could you please provide us some sample data and elaborate your expected result, so that we can make further analysis.

Sample data structure.

ID         Name         Address

1001     a                xxxx

1002     b                yyyy

 

Regards,

Charlie Liao

Patrick1985's avatar
Patrick1985
Frequent Visitor
8 years ago

I've got a list of aircraft dealt with which is in table called platforms list and one column called platforms and looks like;

Platform Name

Apache

ATLAS

Avenger ........

 

I then have another table called Organizations where organizations are tagged with all of the platforms that they work with. Its got a range of different columns but I've put the headings to the ones I care about below.

 

Full Name     ffectedPlatforms

Org 1             Apache; Atlas

Org 2             Avenger

Org 3             Apache; ATLAS; Avenger

 

What I want to do is put a slicer on a page where can filter and bring in ALL the Apache Orgs and references not just the ones that are tagged with Apache only.

 

Hope that helps

 

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

    Patrick1985,

     

    Create a measure in your Organizations.
    Measure =
    var selectedvalue = IF(HASONEFILTER(Platform[Platform Name]),FIRSTNONBLANK(Platform[Platform Name],1),BLANK())
    var check = IF(ISERROR(SEARCH(selectedvalue,MAX(Organizations[ffectedPlatforms]))),0,1)
    return check

    Add this measure to your visual filter.

     

    Regards,

    Chalrie Liao