Forum Discussion

okokokokok's avatar
okokokokok
Regular Visitor
1 year ago
Solved

Bar chart legend drill down

Hello community,

What I am trying to do is fairly simple, but seem to find a way.

This is my current graph:

On the X axis, I have dates
On the Y axis, I have a count
For the legend, I have the different region of my company.

The region have a hierarchy. For now, legend shows by continent, but I want to be able to show by country when only one continent is chosen in the slicer. Basically, the slicer selection should dynamically select continent or country depending on what is selected in the continent slicer.

My guess was to use DAX and the best I came up with is this measure:

DynamicLegend = 
IF(
    HASONEVALUE('Table'[Continent]),
    SELECTEDVALUE('Table'[Country],
    SELECTEDVALUE('Table'[Continent])
))


However, it doesn't work. I think that the reason is that putting a table column in a legend visual is not the same as selecting the values of that column in a measure. If someone can explain the nuance, I'd also be interested.

Kind regards


Need Help
  • MFelix's avatar
    MFelix
    1 year ago

    Hi okokokokok ,

     

    Since you are using the country and continent on the legend there is no drill down option and you need to have a single column for your legend I would do the following:

     

    • Create a new table with a list of all continents and countries
    • Add a column with the type for each one

     

    • Add the following measure:
    Total Value = 
        VAR _country = SELECTCOLUMNS(
			FILTER(
				'Continent/Country',
				'Continent/Country'[Type] = "Country"
			),
			"Country", 'Continent/Country'[value]
		)
		VAR _continent = SELECTCOLUMNS(
			FILTER(
				'Continent/Country',
				'Continent/Country'[Type] = "Continent"
			),
			"Continent", 'Continent/Country'[value]
		)

		RETURN
			IF(
				DISTINCTCOUNT('Fact'[Continent]) = 1,

				COUNTROWS(
					FILTER(
						'Fact',
						'Fact'[Country] IN _country
					)
				),
				COUNTROWS(
					FILTER(
						'Fact',
						'Fact'[Continent] IN _continent
					)
				)
			)

     

    • Format the visual like this:
      • X-axis - Date
      • Y-Axis - Total Value measure
      • Legend - Value from the new table

    Check the result below and in attach file:

     

     

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    1 year ago

    Hi okokokokok ,

     

    Since you are using the country and continent on the legend there is no drill down option and you need to have a single column for your legend I would do the following:

     

    • Create a new table with a list of all continents and countries
    • Add a column with the type for each one

     

    • Add the following measure:
    Total Value = 
        VAR _country = SELECTCOLUMNS(
			FILTER(
				'Continent/Country',
				'Continent/Country'[Type] = "Country"
			),
			"Country", 'Continent/Country'[value]
		)
		VAR _continent = SELECTCOLUMNS(
			FILTER(
				'Continent/Country',
				'Continent/Country'[Type] = "Continent"
			),
			"Continent", 'Continent/Country'[value]
		)

		RETURN
			IF(
				DISTINCTCOUNT('Fact'[Continent]) = 1,

				COUNTROWS(
					FILTER(
						'Fact',
						'Fact'[Country] IN _country
					)
				),
				COUNTROWS(
					FILTER(
						'Fact',
						'Fact'[Continent] IN _continent
					)
				)
			)

     

    • Format the visual like this:
      • X-axis - Date
      • Y-Axis - Total Value measure
      • Legend - Value from the new table

    Check the result below and in attach file: