Parameter slider to remove bars higher than selected value

[PowerBI-Newbie]

Hi,

I have a clustered column chart and I added a parameter slider to the Y-Axis (see image below):

What I want to achieve is when the user changes the slider/value and thus the Y-Axis maximum value, the bars that are more than the selected value should not appear on the graph and the ones that are equal to or less than the selected value should remain. How can I achieve this?

[SamWiseOwl · Accepted solution]

Hi @PowerBI-Newbie 
Create a measure that says:
Show or Hide measure =
If( [SrStatusCalculation] > SelectedValue(table[srStatusSliderMax]), BLANK(), [SrStatusCalculation] )
Use BLANK if you want the column to disappear or 0 to keep the column but show empty.

[PowerBI-Newbie]

Hi @SamWiseOwl ,

Sorry to bother you. How do I take the user selection of certain columns into account? So still using the following measures:

Y-Axis Maximum:

All Faults Count - All Status Y-Axis = IF( ([All Faults Count - All]) > [MaxStatusSlider Value], 0, ([All Faults Count - All] ))

MaxStatusSlider Value (Parameter measure):

MaxStatusSlider Value = SELECTEDVALUE('MaxStatusSlider'[MaxStatusSlider], [MaxChart Overall Failure Status All Faults Count - All])

Fault Count Measure:

MaxChart All Faults Count - All = 
CALCULATE(
    MAXX(
        ADDCOLUMNS(DISTINCT(ALL(Issues[Year:Period],Issues[Status])),"AllCount",[All Faults Count - All]),[AllCount])*1.1,
        All(Issues)
)

So the user may select a particular date period or filter by category so what do I need to change within the measure(s) above to ensure the Y-Axis measure updates accordingly?

[SamWiseOwl]

Hi @PowerBI-Newbie the only thing I see that would be preventing it from changing is the ALL.

All removes internal AND external filters.

AllSelected only removes internal ones (external filters like slicers, filter pane, drillthrough will be kept).

Or AllExcept to remove all filters except the ones on columns you specify.

[PowerBI-Newbie]

Hi @SamWiseOwl ,

This may sound like a stupid question but which ALL do I change into AllSelected/AllExcept?

[SamWiseOwl]

Hi @PowerBI-Newbie 

This bit:

ALL(Issues[Year:Period],Issues[Status])

 Is making sure that all your years and Status are included in the AddColumn, that is making a mini table and for each row working out:[All Faults Count - All]. Do you want to include all the years and status in this mini table?

This one: All(Issues)

is removing every filter on the entire table, not just those two columns. This means you are doing the MAX calculation and including every value.

Put AllSelected on which ever one you want to keep external filters for. Do you want everything included in the [AllCount] measure or do you only want the ones choosen in the slicer.

[PowerBI-Newbie]

Hi @SamWiseOwl ,

This is the graph that I need to have:

The X-Axis is the column Type from the table Issues and Y-Axis is the measure that I mentioned previously. So the X-Axis calculates the number of faults per Type taking into account the external filters/slicers that are applied by the user, one of them being Year:Period (single selection slicer). When I've been playing with the measure to see what effect it's having on the graph, I noticed that the Y-Axis Max is much more than 18.7 (17 x 1.1) - some of the changes that I made is causing it to be in the hundreds and some in the thousands hence my request for assistance as I don't know what I'm doing.

Some of the other graphs that I want to apply this to have a hierarchical X-Axis (Sub-Type and Type columns for example), how will this affect the way the measure should be written?

[SamWiseOwl]

Hello @PowerBI-Newbie 
I thought we were talking about the original visual above. This is a new visual?

This one:

All Faults Count - All Status Y-Axis = IF( ([All Faults Count - All]) > [MaxStatusSlider Value], 0, ([All Faults Count - All] ))

Looks fine, except you might want BLANK() if you want the column to disappear, 0 will still show but be empty.

This one:

MaxStatusSlider Value = SELECTEDVALUE('MaxStatusSlider'[MaxStatusSlider], [MaxChart Overall Failure Status All Faults Count - All])

Doesn't reference the 3rd measure, I'm assuming this is a typo? I'd put 

[MaxChart Overall Y axis - All]) to match below.

Finally this one:

MaxChart All Faults Count - All =
CALCULATE(
MAXX(
ADDCOLUMNS(DISTINCT(ALL(Issues[Year:Period],Issues[Status])),"AllCount",[All Faults Count - All]),[AllCount])*1.1,
All(Issues)
)

Is the one I think might need re-written if it isn't working.

[MaxChart Overall Y axis - All]) =
MAXX ( --Highest row count
    DISTINCT ( --Return each year/issue row once
        SELECTCOLUMNS ( --Load only the 2 columns you need
            ALLSELECTED ( Issues ), --Remove internal filters
            "Year", Issues[Year:Period],
            "Status", Issues[Status]
        )
    ),
    CALCULATE ( CountRows ( Issues ) ) --Return row count, filtering to each Year/Issue
)

I've attached a mock file here.

[PowerBI-Newbie]

Hi @SamWiseOwl ,

Thank you for your response.

That didn't seem to work for some reason. So when I have one field on the X-Axis then it seems to be fine but the moment I have a chart with a hierarchy then it doesn't seem to work. If you see the original graph then you can see that the parameter slider maximum value that the user can slide to is set to 716 and that's because the highest value is 651 and multiply by 1.1 then you get 716:

As for the slider then value then it looks like this:

MaxStatusSlider = GENERATESERIES(0, [MaxChart Overall Failure Status All Faults Count - All], 1)

So the measure [MaxChart Overall Failure Status....] calculates the maximum value and this in turn sets the maximum value. And the parameter measure, [MaxStatusSlider Value...] is:

MaxStatusSlider Value = SELECTEDVALUE('MaxStatusSlider'[MaxStatusSlider], [MaxChart Overall Failure Status All Faults Count - All])

And this is set as the Y-Axis maximum.

So all the above works so far but that's because it's only one field in the X-Axis so the moment that I have two fields and/or there's external date slicers interacting with the visual then the max slider value becomes really big and not 1.1x maximum value. So when you look at the previous graph:

The maximum value is 56 so the slider and Y-Axis should show 1.1x (around 62) but instead it's showing 380, not sure why. The parameter value measure is:

MaxTypeSlider Value = SELECTEDVALUE('MaxTypeSlider'[MaxTypeSlider], [MaxChart Type All Faults Count - Not Cancelled])

And the MaxChart Type.... measure that's referenced above is:

MaxChart Type All Faults Count - Not Cancelled = 
CALCULATE(
    MAXX(
        ADDCOLUMNS(DISTINCT(ALL(Issues[Year:Period],Issues[Type])),"AllCountNotCancelled",[All Faults Count - Not Cancelled]),[AllCountNotCancelled])*1.1,
        All(Issues)
)

And the All Faults Count-Not Cancelled measure that's referenced above is:

All Faults Count - Not Cancelled = CALCULATE(COUNT(Issues[Incident Date]),Issues[Count If not cancelled] = 1)+0

And finally the Y-Axis has the following measure:

All Faults Count - All Type Y-Axis = IF( ([All Faults Count - Not Cancelled]) > [MaxTypeSlider Value], BLANK(), ([All Faults Count - Not Cancelled] ))

So the main thing is the value of the maximum slider to be 1.1x the maximum value of the whatever field is on the X-Axis as well as any external slicers that the user selects.

[SamWiseOwl · Accepted solution]

Hi @PowerBI-Newbie 
Create a measure that says:
Show or Hide measure =
If( [SrStatusCalculation] > SelectedValue(table[srStatusSliderMax]), BLANK(), [SrStatusCalculation] )
Use BLANK if you want the column to disappear or 0 to keep the column but show empty.

[PowerBI-Newbie]

Hi @SamWiseOwl ,

It was actually your video that I was watching and you're the one that responds to my message!

Where do I put the new measure? The graph and details of the measures/columns that I'm using are as follows:

X-Axis is a column from my date table.

Legend is a column from my Issues table.

Y-Axis is a measure:

All Faults Count - All = COUNT(Issues[Incident Date])+0

Y-Axis max is a measure from the parameter:

MaxChart All Faults Count - All = 
CALCULATE(
    MAXX(
        ADDCOLUMNS(DISTINCT(ALL(Issues[Year:Period],Issues[Status])),"AllCount",[All Faults Count - All]),[AllCount])*1.1,
        All(Issues)
)

I gave the above details to give you a bit more detail and for me to know where the new measure will slot in.

[SamWiseOwl · Accepted solution]

Hi @PowerBI-Newbie 

Oh that is awesome, I will be making more videos but got side tracked by having children 😂

The measure will effectively replace the Y axis calculation, so something like this:
If( COUNT(Issues[Incident Date]) > [MaxChart All Faults Count - All], BLANK(), COUNT(Issues[Incident Date]) )

Let me know how you get on 🙂

Also if you didn't know we have full free video courses you can watch!

[PowerBI-Newbie]

Thank you so much @SamWiseOwl , that worked perfectly. I ended up using your first post and adapted that - there was a missing parenthesis somewhere but it worked after I corrected it.

I'll check out your other videos too.

[SamWiseOwl]

Great job, DAX is scary and weird when coming from Excel. After a bit of mucking about it will click!