Help changing measures using slicers to filter charts

[mossshvlw]

Hi all,

I'm trying to use a slicer to filter the data displayed on a chart.  I've followed this youtube video, but still can't get it to work (https://www.youtube.com/watch?v=gYbGNeYD4OY)

Here's what my data looks like, simplified (we'll call this "drug_table"):

ID   Age     Drug

1    14        Methamphetamine Overdose

2    28        Fentanyl Fatality

3    55        Methamphetamine Toxicity

4    45        Cocaine OD

5    32        Methamphetamine OD

Now, I want to create a chart that looks similar to this (  chart example 1  )

BUT, I want to add the ability to filter drug type, using a slicer.  Here's what I've tried:

Step 1:  Create measures for each drug type, that search for drug keyword:

meth = CALCULATE(COUNTROWS('drug_table'),FILTER(ALL('drug_table'),SEARCH("Methamphetamine",'drug_table'[Drug],,BLANK())>0))

cocaine =

CALCULATE(COUNTROWS('drug_table'),FILTER(ALL('drug_table'),SEARCH("Cocaine",'drug_table'[Drug],,BLANK())>0))

fentanyl =

CALCULATE(COUNTROWS('drug_table'),FILTER(ALL('drug_table'),SEARCH("fentanyl",'drug_table'[Drug],,BLANK())>0))

Step 2:  Create a measure table of the measures created:

Step 3:  Create a "Measure Selections" measure using a switch statement:

Measure Selection = IF(ISCROSSFILTERED('Measure Dimensions'[Measure]),

    SWITCH(TRUE(),

        VALUES('Measure Dimensions'[Measure]) = "fentanyl",[fentanyl],

        VALUES('Measure Dimensions'[Measure]) = "meth",[meth],

        VALUES('Measure Dimensions'[Measure]) = "cocaine",[cocaine],

        BLANK()),BLANK())

Step 4: Build a visualization based on the measure selection.  This is where I'm failing.  I tried to add "Measure Selection" to the chart, under "value", and I get this (  chart example 2  )

The chart updates as the measure is changed, but it's only showing the total number of meth cases (the total row count) for each age.  When the measure is changed, I simply want the dataset to be filtered to show only the meth cases by age, i.e. more like chart example 1.  

Can anyone comment on where I'm going wrong?  Hope question makes sense.  Thanks in advance!

[v-shex-msft]

HI @mossshvlw,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[littlemojopuppy]

Hi @mossshvlw 

I think this is more complicated than it needs to be.  If you added a calculated column to your drug_table that is a foreign key to the "drug master" table you created, it would be very easy to get a slicer to work without having to create multiple measures to accomplish this.  Unless I'm missing something...

[mossshvlw]

Thanks.  Can you explain just a bit further the creation of this calculated column?  would I create a calculated column based on the string searching?

[littlemojopuppy]

Hi @mossshvlw 

It could be as simple as this

Drug =
SWITCH(
	TRUE(),
	SEARCH("Methamphetamine",'drug_table'[Drug],,BLANK())>0, "Meth",
	SEARCH("Cocaine",'drug_table'[Drug],,BLANK())>0, "Cocaine",
	SEARCH("Fentanyl",'drug_table'[Drug],,BLANK())>0, "Fentanyl",

	... Continue as Necessary...

	BLANK()
)

Then you could simplify the creation of your drug master table by simply

VALUES('drug_table'[Drug])

Create a 1:M relationship from Drug Master to Drug_Table and it should work

[littlemojopuppy · Accepted solution]

@mossshvlw I made a quick mock up of it with the data you posted at the top of this.  You can download it from here.