Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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
Solved! Go to 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
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
Thanks a million @OwenAuger ,
I had tried distinct earlier but it was crashing on multiselect but VALUES is exactly what I needed.
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |