How to show specific value when select different values in slicer?

[YunJ]

Hi,

I would like to only show Sub-Category="Lip Stick" and "FDT" when choosing slicer Category="Makeup". So I wrote a measure like following, but didn't get the correct result. May I know where I was wrong and how to adjust it?

Thanks a lot!

[ryan_mayu · Accepted solution]

@YunJ 

How many categories do you have?

maybe you can try DAX formula below. Using two if conditions.

Measure = if(SELECTEDVALUE('Table (2)'[category])="skincare",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="Eye care" ||'Table'[sub-category]="Toner")),if(SELECTEDVALUE('Table (2)'[category])="CATEGORY B",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="AAA" ||'Table'[sub-category]="BBB")))

[ryan_mayu]

@YunJ 

Maybe you can try something like below.

measure = if( selectedvalue(category[catetory])="Makeup", calculate(sum(amount),filtering(category,category[sub-category]="Lip Stick" || category[sub-category]="FDT"))

Since you didn't share the sample file or raw data screenshot, you need to slightly modify the DAX formula.

[YunJ]

Hi @ryan_mayu 

Thanks a lot for your solution. It should be "filter" rather than "filtering" right?

Also, is there other ways to only write measures on Sub-category rather than sum(amount)?

Thanks for your time!

[ryan_mayu]

@YunJ 

Yes, you are correct. Sry about the typo error.

I saw you used MAX in your formula, you can change sum to max as well.

Measure = if(SELECTEDVALUE('Table (2)'[category])="makeup",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="lip stick" ||'Table'[sub-category]="FDT")))

[YunJ]

Thanks @ryan_mayu 

But I also have logic such as for Category="Skincare".

For example, when select slicer Category="Skincare", I only want Sub-Category="Eye care", "Toner". So It seems like I need to write another measure like following, and both these measures to the visual?

Measure = if(SELECTEDVALUE('Table (2)'[category])="skincare",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="Eye care" ||'Table'[sub-category]="Toner")))

 So I'm wondering can I only write measure only about Sub-Category? Something like the following?

Measure = if(SELECTEDVALUE('Table (2)'[category])="skincare",THEN 'Table'[sub-category]="Eye care" ||'Table'[sub-category]="Toner",ELSEIF(SELECTEDVALUE('Table (2)'[category])="makeup",THEN 'Table'[sub-category]="Lip stick" ||'Table'[sub-category]="FDT")))

 Thanks for your patience.

[ryan_mayu · Accepted solution]

@YunJ 

How many categories do you have?

maybe you can try DAX formula below. Using two if conditions.

Measure = if(SELECTEDVALUE('Table (2)'[category])="skincare",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="Eye care" ||'Table'[sub-category]="Toner")),if(SELECTEDVALUE('Table (2)'[category])="CATEGORY B",CALCULATE(MAX('Table'[amount]),FILTER('Table','Table'[sub-category]="AAA" ||'Table'[sub-category]="BBB")))

[YunJ]

Hi @ryan_mayu Thanks for your solution.

I don't have too many categories, but actually I have 5 measures, so I have to write for each measure right?

Thanks

[ryan_mayu]

@YunJ 

You can write in one measure. You need 5 if selectedvalue clause.

if selectedvalue = "category A", maxx(xxxx), ifselectedvalue = "category B", maxx(xxx), if(seletedvalue = "category C",maxx(xxxx), if(seletectedvalue="category D",maxx(xxx), ifselectedvalue ="category E",maxxc(xxxx))))

I am not if we can have better solution. Maybe we can see if anyone else have better idea on this.

[YunJ · Accepted solution]

Hi @ryan_mayu Thanks for your explanation.

What I try to say is maybe I have different maxx(xxx), maxx(yyy), maxx(zzz). So maybe I have to write not only one measure right?

For example:

if selectedvalue = "category A", maxx(xxx), ifselectedvalue = "category B", maxx(xxx), if(seletedvalue = "category C",maxx(xx), if(seletectedvalue="category D",maxx(xxx), ifselectedvalue ="category E",maxxc(xxx))))

if selectedvalue = "category A", maxx(yyy), ifselectedvalue = "category B", maxx(yyy), if(seletedvalue = "category C",maxx(yyy), if(seletectedvalue="category D",maxx(yyy), ifselectedvalue ="category E",maxxc(yyy))))

if selectedvalue = "category A", maxx(zzz), ifselectedvalue = "category B", maxx(zzz), if(seletedvalue = "category C",maxx(zzz), if(seletectedvalue="category D",maxx(zzz), ifselectedvalue ="category E",maxxc(zzz))))

[ryan_mayu]

@YunJ 

Yes, you can do that and you need to drag three measures into the visual. Depends on how you want to show the results in the visual.

You can have a try to see if the result is what you want.