Bottom 25 Percent

[microsoftrookie]

Hello,

I would like to make a bottom 25 top n graph, however i would only like it to appear to give results when both the slicers are selected. My measure currently reads as "IF(AND(DISTINCTCOUNT(inddir[Code+Name])=1, DISTINCTCOUNT(Sheet1[Geography])=1),SUM(iomatrix[Percent Change]),"NA") This gives me the current result of when one slicer is selected. This is not an or statement so why am I recieveing this result. On the top 25 i use the same code but it works properly giving me only the result if I only select both. 

Have i done something incorrectly? Thank you all ahead of time for your help.

Regards,

Microsoft Rookie 

[v-jiascu-msft]

Hi @microsoftrookie,

The cause could be the context. The condition of IF would be evaluated in its context. It returns true when you select one slicer. Maybe you can try to use HASONEFILTER, HASONEVALUE, ISFILTERED. The screenshots are too small and incomplete. Please post a sample in TEXT mode. The .pbix file would be great. 

Best Regards!

Dale

[microsoftrookie]

Unfortunately I am unable to post the entire .pbix file due to company policy; however, I can try to give you a bit more context. The column I am sorting by the top N filter is a numeric column like so-  and they are indexed by a seperate text column, but I need to show the bottom 25 values in this column. I need my bar chart to show the bottom 25 if and only if my two slicers shown here,Both of these should be selectedare selected at the same time. So for example, if I were to select the geography slicer and only the geography slicer, the previous bottom 25 bar chart should remain blank. If they are both left blank, then they should remain blank. And only if they are both selected should they return a result such as the following. A working model from a seperate report This is an example of one that works by using the same idea with the exception that it only responds to one filter in the formula which is the following %changeIndustrypage = IF(DISTINCTCOUNT(Sheet1[Geography])= 1, SUM(iomatrix[Percent Change]),"N/A").

Perhaps I have done something wrong in the filters on the tooltip menu on the side and this is how it is currently set up-Just the Values and the Measure used Filters used and howAs you can see I have my Percent Change Column being filtered by the bottom 25 using the Top N filter by the measure I am attempting to use. A text sample i could work with would be something like if and only if 2 or more slicers are selected by the user the bar chart will show the bottom 5 values of an numeric column, otheriwse the bar chart will not fill in the bars and remain blank, like so Remaining Blank when one is selected.
Thank you again for all your time and help with my question. I'm not entirely sure what you mean by text mode, but I've done my best to explain it here. Thank you again,

Microsoftrookie.

[v-jiascu-msft]

Hi @microsoftrookie,

We can leave TEXT mode alone since you can't share your data. Tips here, a measure is needed. We need to use HASONEFILTER, ISFILTERED, HASONEVALUE to check out if two slicers are selected. Different combination, different outcome. Maybe the formula looks like this:

Measure = if(hasonefilter(slicer 1)&hasonefilter(slicer 2), (actions), (actions))

Best Regards!

Dale

[microsoftrookie · Accepted solution]

Hello Dale, 

Thank you for your help I did not realize the HASONEFILTER, ISFILTERED, and HASONEVALUE existed. It works, however not as you originally thought with the & symbol but rather using the AND function like so, IF(AND(HASONEFILTER(table1(examplecolumn),HASONEFILTER(Sheet1[Example2])),SUM(table3[Heresanotherexample3]),"N/A")

Using the ampersand symbol like in the example you posted before gave an error about not being able to handle a true false statement claiming that it would be instead a true true statement. The message says "Mdxscript(Model) (28,49) Calculation Error in measure [(insert measure table and name here)]: Cannot Convert value of 'TRUETRUE' of type text to True/False" My guess is that the ampersand format changes the filter argument to say if they are true, do this but "if true(which should be if false)" do this and the IF statement breaks.

Thank you for all of your help and identifying new functions for me; I was able to come up with a solution. 

Regards,

Microsoftrookie