Calculate thresholds and use it in a slicer

[AnthNC]

Hi everyone,

Here's the project :

I'm showing on a line graph the evolution of the production in volumes (tons) and values (millions of francs) of several products for 2 regions (NC and PS).

There are 2 measures by region :

- total values NC and total volumes NC

- total values PS and total volumes PS

Those 4 measures have been regrouped in a field parameter.

What I'm trying to do :

Instead of the "Years" slicer circled red in the screenshot, I would like to have volumes or values thresholds that will filter and show only the product categories and subcategories which are in the selected thresholds.

In this example, I've filtered out all product categories inferior to 8000 MF :

Thing is, I can't show filter panes in this report. So the end user will not be able to use it.

Would you know a way to use a slicer like the filter pane ? 

Here's the pbix file :

https://drive.google.com/file/d/1a9eT8GAJ_Dcal5w24Yo1nrf-X1uKSAId/view?usp=sharing 

Thanks in advance 🙂

[MFelix · Accepted solution]

Hi @AnthNC ,

You can create a numeric parameter that will allow to do a filter based on the selected value then you need to add a measure similar to this:

Filter = VAR selectedMeasure =
		SWITCH(
			SELECTEDVALUE('Paramètre Measures'[Paramètre Commande]),
			0, [Total ValueNC],
			1, [Total VolumeNC],
			2, [Total ValuePS],
			3, [Total VolumePS]
		)
		RETURN


			SWITCH(
				SELECTEDVALUE('Paramètre Categories'[Paramètre Commande]),
				0, COUNTROWS(FILTER(
					SUMMARIZE(
						'local production',
						'local production'[Product category],
						"ValueSelected",
 selectedMeasure
					),
					[ValueSelected] >= Parameter[Parameter Value]
				)),
				1, COUNTROWS(FILTER(
					SUMMARIZE(
						'local production',
						'local production'[Product subcategory],
						"ValueSelected", selectedMeasure
					),
					[ValueSelected] >= Parameter[Parameter Value]
				))
			)

Then add it in the visualization and select is not blank:

See pbix file attach

[AnthNC]

Thanks @MFelix 

It works great.

Is there a way to do the same slicer but with a min and max value, just like the year slicer where you can input 2 values :

[MFelix]

Hi @AnthNC,

Yes you need to change the slicer to a between instead of single select. 

But if you are doing this you probably would need to change the metric to be greater than or less than. 

And don't forget to accept the correct answer to help out others