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 Am...
  • 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:

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.

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hey Barthel ,

 

The problem with this is that the (Blank) is depended for the region. So in the 'country' field there is no blank, only in the Region fields. But then when I select Americas and is not equal to blank the other regions will only show the total of americas as blank. So I can't take the Blank out of the visual by using the filter panel.

 

BUT I've found a solution with DAX.....I love DAX 

 

What I did for the blanks is to create a "If(Selectedvalue("  and then for each 'Order' in the parameter a calculation measure where I filter out the blank for that region. 

 

So:       IF(SELECTEDVALUE('Parameter'[Parameter order])=1, CALCULATE([Margin], FILTER(Dim_Country_Region,Dim_Country_Region[A]<>BLANK())),

IF(SELECTEDVALUE('Parameter' [Parameter Order])=2, CALCULATE([Margin], FILTER(Dim_Country_Region,Dim_Country_Region[B]<>BLANK())),

IF(SELECTEDVALUE('Parameter' [Parameter Order])=3, CALCULATE([Margin], FILTER(Dim_Country_Region,Dim_Country_Region[C]<>BLANK())),

[Margin]))))

 (hope this way, I made the DAX a bit readable/clear)

And that made the blanks disappear! 

 

So thanks for the solution!