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

here's another crazy idea approach

 

add a semicolon to the front and the back of your enumeration.

 

for example instead of 

 

United States;Asia

 

this would be

 

;United States;Asia;

 

That helps to prevent bleedover.

 

Next, use DAX FIND() or SEARCH() functions with that string against your Mapping columns, First against the Country column and then (if nothing found) against the region column

 

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