Constant Y line based on slicer in Line graph

[Aidenn90]

Hi,

I haver a line graph that shows various values based on date. There are two slicers used to control this graph.

Slicer #1: Date

Slicer #2: Type 

Is there there a way to create a measurement or function to have constant Y lines in a line graph adjust based on slicer #2. (Date should have no control over the data i want returned)

Example below:

Query 1
Type	LSL	USL
A	5	20
B	6	21
C	7	22
D	8	23
E	9	24
F	10	25

Query 2
Type	Date		Value
A	1/1/1990		2
A	1/2/1990		11
A	1/3/1990		19
A	1/4/1990		29
B	1/1/1990		3
B	1/2/1990		12
B	1/3/1990		20
B	1/4/1990		30
C	1/1/1990		4
C	1/2/1990		13
C	1/3/1990		21
C	1/4/1990		31
D	1/1/1990		5
D	1/2/1990		14
D	1/3/1990		22
D	1/4/1990		32

All my data comes from 'Query 2'. I have graphed [Value] against [Date], and use [Date] and [Type] as a slicer. I would like put a constant Y Axis Line using the [LSL]/[USL] column from 'Query 1' as it slices by Query1 [type].

Example of what My graph currently looks like. I would like the Dotted blue lines to change to new spec limits when i change the blue slicer from D to another letter. Currently they are set at 8 and 23, i would like them to automatically change to 5 and 20 when i got to Letter A, or 6/21 when i go to be, and so on and so forth. 

Thanks,

[Ritaf1983]

Hi @Aidenn90 

Can you show the desired result?

[Aidenn90]

Hi Rita, 

Thanks for the quick response. I have updated my original post with example of graph with constant Y-lines that i would like to change with my slicer.

[Ritaf1983 · Accepted solution]

Hi @Aidenn90 ,

If I understand your question correctly, the solution is fairly straightforward. You can create two measures for the constant lines (LSL and USL) using SELECTEDVALUE. These measures will dynamically adjust based on the Type selected in the slicer.

Here’s how you can do it:

Create Measures for LSL and USL:
Use the following DAX formulas to retrieve the values from Query 1 based on the slicer selection:

DAX
Copy code
LSL = SELECTEDVALUE('Query 1'[LSL])
USL = SELECTEDVALUE('Query 1'[USL])
Add Constant Lines to the Graph:
After creating these measures, you can add them as constant lines in the Analytics pane of your line chart (as shown in the attached image).

The pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

[Aidenn90]

Exactly what i needed, i was trying to make it more complicated then i needed to. Thank you

[Ritaf1983]

Happy to help💗