Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Solved! Go to Solution.
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")))
Proud to be a Super User!
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))))
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.
Proud to be a Super User!
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!
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")))
Proud to be a Super User!
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.
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")))
Proud to be a Super User!
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
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.
Proud to be a Super User!
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))))
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.
Proud to be a Super User!