Forum Discussion

VictorSunrise's avatar
VictorSunrise
New Member
2 years ago
Solved

Help to Count Number of Locations [Project Count]

I've been trying to count the number of projects in the locations table here, but nothing seems to work when commas are present.  Is there any way to do this using CONTAINSSTRING?  I don't want to add rows or columns, unless that won't affect the underlying data tables as those are used for data entry and contain many columns that don't need to be duplicated. 

 

Projects Table

Project NameProject Location
Sand Sieving ProjectZuma Beach, Hermosa Beach, Santa Monica Beach
Crab MonitoringZuma Beach
Restroom RehabHermosa Beach

 

Locations Table

Project LocationLatLonProject Count
Zuma Beach  2
Hermosa Beach  2
Santa Monica Beach  1


This was my last effort that did not work as expected: 

 

 

Project Count =
CALCULATE(
COUNTROWS('dbh Projects1'),
FILTER(
'dbh Projects1',
CONTAINSSTRING(
'dbh Locations4'[Project Location],
'dbh Projects1'[Project Location]
)
)
)

 

This counts locations that are alone, but does not consider those where there are more than one with commas.  


Thank you,

 

Vic

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi VictorSunrise ，

     

    I made simple samples and you can check the results below:

    Count = 
VAR _ADD = ADDCOLUMNS(ALLSELECTED('Table'),"Flag",IF(CONTAINSSTRING([Project Location],SELECTEDVALUE('DimProject Location'[Project Location])),1,0))
RETURN
SUMX(_ADD,[Flag])

     

    Hope it helps!

     

    Best regards,
    Community Support Team_ Scott Chang

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies