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 )
)

     

     

msmays5's avatar
msmays5
Helper II
6 years ago

Thanks, lbendlin. In PowerQuery, I prepended a semicolon. I then created the below measure, which works. However, I have one final question.

 

In addition to showing the below value where the visual provides a filter context to a single market, I'd also like to have a card that will show the maximum value for any of the selected markets. Put another way, if a user selects China and United States, the card would compute the below for both United States and China, and then take the maximum of that value. 

 

How can I modify the below to work with an iterator (which is what I think I need?)

 

 

VAR _country =
    SELECTEDVALUE ( Mapping[Country], "Multi" )
VAR _countrySemiColon = 
    IF ( _country <> "Multi", CONCATENATE(";", _country), "Multi" )
VAR _region =
    IF (
        _country <> "Multi",
        LOOKUPVALUE (Mapping[Region], Mapping[Country], _country),
        "Unknown"
    )
VAR _regionSemiColon = IF ( _country <> "Multi", CONCATENATE(";", _region), "Unknown" )
RETURN
    IF (
        _country <> "Multi",
        COUNTROWS (
            FILTER (
                Article,
                SEARCH ( _countrySemiColon, Article[Geography_Tags],, 0 ) <> 0
                    || SEARCH ( _regionSemiColon, Article[Geography_Tags],, 0 ) <> 0
            )
        ),
        "Multiple Countries are selected"
    )

 

 

 

msmays5's avatar
msmays5
Helper II
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 )
)