Dynamic Table filtering - Limiting the X-Axis of a chart

[DJBAJG]

I've got a single table of data that I need to make a line chart from. This line chart will show financial values (ISL Level) on the X-Axis and some projection numbers of the Y-Axis (Client Projection, High Estimate, Low Estimate). The chart output will be dictated by the input from a parameter filter on the page. This parameter is acting on a column from Sheet1 (Number of Employees). Very simple so far. Now comes the hard part. I need to have the parameter ALSO dictate the X and Y-Axis limits.

I was able to build out a Y-Axis limit measure but I cannot seem to do anything for the X-Axis.

My thought is to somehow dynamically add an indicator column (1,0) to Sheet1 and then use it as a visual level filter. I'm not able to do that.

I also thought about creating another seperate table that has just employee count and the required low and high values for the X-Axis but you can't dynamically filter a table so that doesn't work either. 

Pitures and some text are not sufficient to get this one solved so I built out a small example of the dataset and included the PBIX here. Please take a look and see what can be done. I've spend nearly a week on this one and am getting a bit frustrated.

Thanks!

X-Axis Limits 

[rajasaadk_98]

To achieve dynamic control over both the X-Axis (ISL Level) and Y-Axis (Client Projection, High Estimate, Low Estimate) in a Power BI line chart, while also filtering based on a parameter (such as the "Number of Employees"), here’s a general approach you can follow:

Steps:
Create a Measure for Y-Axis Limits: You mentioned you have already created a Y-Axis limit measure. Ensure this measure dynamically adjusts the minimum and maximum values of the Y-Axis based on the parameter input (Number of Employees). If you haven't, you can create a measure like this:
MinYValue = MINX(FILTER(Sheet1, [Number of Employees] = SELECTEDVALUE(Sheet1[Number of Employees])), [Low Estimate])

Similarly, create another for the max Y-value:

MaxYValue = MAXX(FILTER(Sheet1, [Number of Employees] = SELECTEDVALUE(Sheet1[Number of Employees])), [High Estimate])
Dynamic X-Axis Filtering Using Measures: Since Power BI does not directly support dynamic axis ranges, we can use a filtering mechanism. To control the X-Axis limits, follow these steps:

Create a measure to dynamically check if a value falls within the selected range of ISL Levels based on "Number of Employees":
XAxisIndicator = IF(
[ISL Level] >= MINX(FILTER(Sheet1, [Number of Employees] = SELECTEDVALUE(Sheet1[Number of Employees])), [ISL Level])
&& [ISL Level] <= MAXX(FILTER(Sheet1, [Number of Employees] = SELECTEDVALUE(Sheet1[Number of Employees])), [ISL Level]),
1,
0
)

Add a Visual-Level Filter to Limit X-Axis:

Use the XAxisIndicator measure as a filter on the visual (line chart). Set the filter condition to show values where XAxisIndicator = 1. This will dynamically filter the X-Axis based on the selected range from your parameter.
Adjust Visual-Axis Range:

Once your data is filtered using the steps above, Power BI will automatically adjust the X-Axis and Y-Axis limits to reflect the data points displayed on the chart

[DJBAJG]

The "XAxisIndicator" measure you propose doesn't work. ISL Level is a column not a measure and therefore cannot be used the way your using it as the first argument in the IF statement. Did you try this measure in the sample pbix I included? Were you able to get it to work? 

EDIT: Actually, the measure you created isn't going to work at all. It does not take into account the requirement of the low and high deductible columns being used to determine the X_Axis end points. The more I lok at it the more I think the way the data is structured it may not even be possible. I'm open to changing the layout so any solutions are on the table. Please help!

[amitchandak]

@DJBAJG . X-axis you can only give a limit if it is a Numeric or date field (Continuous axis). If it is a categorical field you have to control it by slicer

[DJBAJG]

Not sure I'm following you here. I don't think it matters how the chart is set up (continuous vs. Categorical). The ISL component of the cahrt is not being filtered on a visual level but rather the data should be filtered at the column level with an indicator that would then be used as a viual filter as rajasaadk_98 has tried to accomplish although there seems to be an issue with the measure. I appriate the input for sure and still really need to get this solved.

Thanks. 

[DJBAJG · Accepted solution]

I figured it out. Way easier than I was making it. All it required was a custom column comparing the ISL level to the high and low deductible. Put a 1 in the column where it meetsthe requirements and filter the visual by the new column. Also, it ONLY works with the chart's X-Axis set as categorical.