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 AirseekerChinookBanshee. I've added a new table in which has all unique aircraft types and I want to count how many times each one appears. 

 

    Everytime I've tried this I come back with only the times the unique value appears ignoring all the ones that are part of a wider text string or a count of the total number of rows in the original data source. 

 

    I can post examples of the data if required. 

Need Help

6 Replies

    • MarcelBeug's avatar
      MarcelBeug
      Community Champion
      8 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
        8 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