Dynamic Title with non unique column values

[Anonymous]

Hi, 

I am trying to create a dynamic title for a report based on a multi-select filter.  The filter is a country filter using the SalesTerritory Table of the Adventure Works dataset.

The DAX formula I am using is:

Country Title = IF(ISFILTERED('Dim SalesTerritory'[FlagURL]), "Sales by Country: " & CONCATENATEX('Dim SalesTerritory','Dim SalesTerritory'[SalesTerritoryCountry], ", ") , "Sales by Country: All Countries")

This works fine when I select any country but the United States:

The issue I am running into is that the United States is broken up into Northwest, Northeast, Central, Southeast and Southwest in this table which is causing the title to show United States 5 times as shown below:

I've tried creating an additional table with the list of unique countries and creating a relationship between the SalesTerritory table and this lookup table and using the country from this table as part of the DAX above but this isn't working.

Any help on this would be gratefully appreciated. 

Thanks, 

Vav

[OwenAuger · Accepted solution]

Hi Vav,

You can use a either VALUES or DISTINCT to return the distinct values of the SalesTerritoryCounty column and use that as the first argument of CONCATENATEX.

Try the below code (I just changed the first argument of CONCATENATEX):

Country Title =
IF (
    ISFILTERED ( 'Dim SalesTerritory'[FlagURL] ),
    "Sales by Country: "
        & CONCATENATEX (
            VALUES ( 'Dim SalesTerritory'[SalesTerritoryCountry] ),
            'Dim SalesTerritory'[SalesTerritoryCountry],
            ", "
        ),
    "Sales by Country: All Countries"
)

Regards,

Owen

[Anonymous]

Thanks a million @OwenAuger , 

I had tried distinct earlier but it was crashing on multiselect but VALUES is exactly what I needed.