Forum Discussion

Patrick1985's avatar
Patrick1985
Frequent Visitor
9 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
MarcelBeug's avatar
MarcelBeug
Icon for Community Champion rankCommunity Champion
9 years ago

A solution in the bottom query below; the other queries return example data.

 

Query Data:

 

= #table(type table[tag = text],{{"AirseekerChinookBanshee"},{"AirseekerChinook"},{"Chinook"},{"BansheeAirseeker"}})

Query Aircrafts:

 

= #table(type table[Aircraft = text],{{"Banshee"},{"Chinook"},{"Airseeker"}})

 

Query AircraftCounts:

 

let
    Source = Aircrafts,
    #"Added Custom" = Table.AddColumn(Source, "Count", (CurrentAircraft) => List.Sum(Table.TransformRows(Data, each if Text.Contains(_[tag],CurrentAircraft[Aircraft]) then 1 else 0)))
in
    #"Added Custom"
Patrick1985's avatar
Patrick1985
Frequent Visitor
9 years ago

Hi,

 

   Thanks for getting back to me, you'll have to excuse my ignorance I've only just started messing around with Power BI and so still very much finding my way. For instance I didnt even realise could set up a query direct from the data and up to now have just used measures and calculated columns. Is there not a solution whereby I can just add another column next to the list of all the aircraft that will look up and count instances in another data connection. I've had a couple of goes to get this to work and not really making any headway.

 

    To help make it clearer my data source for the list of Organizations that are tagged with Aircraft is AWET with a column called AffectedPlatforms, and my master list of aircraft is the Platforms list with a single column in it called Platorms.

 

Many thanks

 

 

  • 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