Forum Discussion
Helper IVParameter slider to remove bars higher than selected value
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.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!
Super UserHi 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.
Helper IVHi SamWiseOwl ,
This may sound like a stupid question but which ALL do I change into AllSelected/AllExcept?
- SamWiseOwl
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.