Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic Names axis based on selected filter.

Hi All,

 

I've a question about a dynamic axis in charts. I have sales data from different countries. To put them all in a chart is a bit too much so I have sorted the countries into regions. So Americas, Europe, Africa and Asia. What I would like to show is that the chart shows the margins of all regions (coming from Dim_Countries[Region]) together, but when the user select a region in the filter the chart shows the country (coming from Dim_Countries[Country]) of the selected region. 

 

I have something fimiliar with quantities and amounts with Selected value but then I need to do a calculation. now it has to choose between two fields with names (Dim_Countries[Region] or Dim_Countries[Country]). How can I set this?

 

I've tried this with parameters and that work but now the users need to switch from regions to countries manually. So they have to select a region and change the view to countries manually. How can I set this automatically when the users select a region?

 

  • Barthel's avatar
    Barthel
    3 years ago

    Hey Anonymous,
    This is possible by creating a table in which the regions are columns and the rows are countries. And then create a field parameter on it. I used the following sample data:

    Dim_Countries:

    CountryRegionSales

    1A8
    2A12
    3A54
    4B21
    5B76
    6B32
    7B89
    8B21
    9C40
    10C4

    Dim_Region:
    Region

    A
    B
    C

    Dim_Country_Region (combination table in which you indicate per country to which region it belongs. You could create this table dynamically in M ​​by pivoting the region column in the countries table):

    CountryABC

    11  
    22  
    33  
    4 4 
    5 5 
    6 6 
    7 7 
    8 8 
    9  9
    10  10

    Field paramter (refer to the general region column and call it 'All'. And select all the region columns from the combination table underneath):

     

     

    Parameter = {
    ("All", NAMEOF('Dim_Region'[Region]), 0),
    ("A", NAMEOF('Dim_Country_Region'[A]), 1),
    ("B", NAMEOF('Dim_Country_Region'[B]), 2),
    ("C", NAMEOF('Dim_Country_Region'[C]), 3)
}

     

     

    This is how the models looks:

    Use the field parameter as a slicer. And also place the field parameter on the x axis of a visual.

    'All' selected:

    When you select one region:

5 Replies

  • Barthel's avatar
    Barthel
    Solution Sage
    3 years ago

    Hey Anonymous,
    This is possible by creating a table in which the regions are columns and the rows are countries. And then create a field parameter on it. I used the following sample data:

    Dim_Countries:

    CountryRegionSales

    1A8
    2A12
    3A54
    4B21
    5B76
    6B32
    7B89
    8B21
    9C40
    10C4

    Dim_Region:
    Region

    A
    B
    C

    Dim_Country_Region (combination table in which you indicate per country to which region it belongs. You could create this table dynamically in M ​​by pivoting the region column in the countries table):

    CountryABC

    11  
    22  
    33  
    4 4 
    5 5 
    6 6 
    7 7 
    8 8 
    9  9
    10  10

    Field paramter (refer to the general region column and call it 'All'. And select all the region columns from the combination table underneath):

     

     

    Parameter = {
    ("All", NAMEOF('Dim_Region'[Region]), 0),
    ("A", NAMEOF('Dim_Country_Region'[A]), 1),
    ("B", NAMEOF('Dim_Country_Region'[B]), 2),
    ("C", NAMEOF('Dim_Country_Region'[C]), 3)
}

     

     

    This is how the models looks:

    Use the field parameter as a slicer. And also place the field parameter on the x axis of a visual.

    'All' selected:

    When you select one region:

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Oh thanks a lot this works. I only have one issue with this method. I now see the others as blank in my chart.

      So the total is always the same (total of the entire world). 

      I think this has to do with the Dim_Country_Region where country 4 belongs to region B but not to region A or C. if A is selected he shows the figures of 1, 2, 3 and blank (which is 4 till 10). In the table these fields (4 till 10) are 'null' for region A. But I have no idea how to get rid of the 'others' in the chart. 

      • Barthel's avatar
        Barthel
        Solution Sage
        3 years ago

        Hey Anonymous,

        Yeah you are right. I think the easiest way to fix this is to filter the visual, where 'country' is not equal to 'Blank'. You can do this in the filter panel once you've selected the visal.