Forum Discussion

msmays5's avatar
msmays5
Helper II
6 years ago
Solved

Count Rows Based on Disconnected Tables

I have a 'Mapping' table with the following columns: Mapping[Country] and Mapping[Region].   I have a second table 'Articles' that has two columns: Article[ID] and Article[Geography_Tags]. The Arti...
  • msmays5's avatar
    msmays5
    6 years ago

    I was able to solve this. For anyone who comes across this:

     

    Count of Articles =
MAXX (
    Mapping,
    VAR _countrySemiColon =
        CONCATENATE ( ";", Mapping[Country ] )
    VAR _regionSemiColon =
        CONCATENATE ( ";", Mapping[Region Tag] )
    VAR _FilteredTable =
        FILTER (
            Articles,
            (
                SEARCH ( _countrySemiColon, Article[Geography_Tags],, 0 ) <> 0
                    || SEARCH ( _regionSemiColon, Article[Geography_Tags],, 0 ) <> 0
            )
        )
    RETURN
        COUNTROWS ( _FilteredTable )
)

     

     

lbendlin's avatar
lbendlin
Super User
6 years ago

Change the semicolons to pipe "|" and attack your enumerations with the PATH functions.

 

Does it really matter if you match to country or region?  If the combined list of countries and regions is unique (ie no two countries have the same region etc)  then you could combine you Mapping columns 

 

And lastly, use SWITCH() to exit the search after the first hit.

msmays5's avatar
msmays5
Helper II
6 years ago

Thanks for the response, lbendlin. There are multiple countries that have the same Region (the list of two items I included in my example was just a short list). I have a list of 95 countries, each of which belongs to a single region.

 

Because a value in Article[Geography_Tags] could contain the country, the region, or both, my thought was to filter the Article table to include only rows where Article[Geography_Tags] contains either the selected Mapping[Country] or the country's Mapping[Region]. But I think that's where I'm getting hung up