Group options into a simpler series of slicer choices

DAX DAX Calculations
1

  1. I have a data source that contains a series of locations, e.g. London, Manchester, Adelaide, Melbourne, Washington.

  2. I would like to present the user with a choice of countries., i.e. England, Australia, USA.

  3. One method is to add a new column to the data source, but I am not keen on this as I would like it to be more transparent to the end user.

  4. Is there a way I can do this in DAX. ie create somehting that can then present as a slicer. e.g. England = (London, Manchester).

2

Hi @kellysolutions ,

Maybe you could create lookup table with Location + Country in the middle ?

For further exploration of that idea - you can use EDNA Data Menthor - Tools Advisor. Create a simple query already based on that topic so you could check at:

3

hey @kellysolutions ,

That scenario can be resolved from a Data Modeling perspective, without any DAX.

You can use a dimension table for cities DimCities, containing a column for “City” and one for “Country”.

| City        | Country     | Others...
| ----------- | ----------- |
| London      | England     |
| Manchester  | England     |
| Adelaide    | Australia   |
| Melbourne   | Australia   |
...

Then you connect the City column to your data source’s “location” column and you use the Country Column as a dimension in visuals and/or slicers.

4

Just another thought on this. is to go the internet and download all the countries you need along with cities involved in the countries into its own dimension table and reference that with the your fact table.

5

If you want to use the country info as a slicer - I think you’re going to need country as a column in your dataset. This can be a disconnected table, or it can be part of your table that contains the locations. I don’t think you can create a slicer based on DAX output.

Now, if you want your user to know what cities are contained in the country, that’s do-able with a bit of DAX magic, but it might create a very long output.