Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
msmays5
Helper II
Helper II

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 Article[Geography_Tags] field contains a list of countries and/or regions that were tagged for that article (separated by semicolons). For example, the below are some values in this column:

  • United States;China
  • United States;North America;Asia;United Kingdom

Because there is no common key between the Mapping[Country] and Article[Geography_Tags] columns, there is not a relationship between the two tables.

 

On the table visual, I have a list of selected Mapping[Country]. For each selected Mapping[Country], I'd like to calculate the number of Articles for which that country (or country's region) was tagged.

 

Based on the above italicized values in the Articles[Geography_Tags] column, I would expect the following results:

  • United States: 2 (notice that for the second entry above, United States get credit for the second entry only one time even though the country and region were both tagged)
  • North American countries other than United States: 1
  • China: 2 (because China is part of Asia, it would get credit for both articles)
  • Asian countries other than China: 1
  • United Kingdom: 1

Since the calculation depends on the filter context of Mapping[Country], I assume this should be a measure, but after that I'm a little stuck. Any and all help is greatly appreciated!

1 ACCEPTED SOLUTION

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

 

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

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.

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

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

 

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

 

 

 

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.