Choose between measures in a slicer

[Anonymous]

Hi,

In a table, I have sales in Value, Volume and Units in different columns. In the table and/or chart, I would like to be able to choose if I want to see Value, Volume or Units in a filter of a slicer

Is that possible?

[Anonymous · Accepted solution]

Hi @Anonymous,

You can refer to below steps to achieve your requirement:

1. Create a Selector table as source of slicer.

Selector = UNION(ROW("Type","Amount"),ROW("Type","Value"),ROW("Type","Unit"))

2. Write measure to original table to choose display item based on slicer.

Dynamic Display = 
var selectitem=IF(HASONEVALUE(Selector[Type]),VALUES(Selector[Type]),BLANK())
return
SWITCH(selectitem,"Amount",MAX('Table'[Amount]),"Unit",MAX('Table'[Unit]),"Value",MAX('Table'[Value]),BLANK()) 

3. Create table visual and slicer.

Result:

Regards,

Xiaoxin Sheng

[jalj]

Great and useful answer, thanks. 

how would you then make the column header displays the name of the slicer selection?

[kinga]

How do you create the "Type" table that the selector table is referencing?

[Anonymous]

Go to "Modeling"

Select "New Table"

Copy / paste the text into the DAX code line

Change the code as needed and press enter... You can add more lines in the table later

[kinga]

Thank you - I have figured out another method.

[Anonymous]

FANTASTIC!!! Works very well. Thank you very much!

[fhill]

I watched this training recently, and I belive they are doing something simular to what you are asking.  They use a slicer to choose between 3 values on a line chart.

http://pragmaticworks.com/Training/Details/Advanced-DAX-Problem-Design-Solution

Forrest

[Anonymous]

Hi Fhill,

This also a good alternative - and will use this as well as it works well with measures. One issue though is that in tables the measures will still be in the table columns when it is deselected - just with no values, If one have a lot of measueres that dose not work very well.

- But it charts it's perfect!

Kent

[Anonymous]

Hi,

In a table, I have sales in Value, Volume and Units in different columns. In the table and/or chart, I would like to be able to choose if I want to see Value, Volume or Units in a filter of a slicer

Is that possible?