cancel
Showing results for
Did you mean:
Frequent Visitor

## Is it possible to have one persistent line to compare all other lines against?

We have data coming in at England level as well as more granular geographies. We would like to have the England line in our line chart visible regardless of the filters applied to the chart, so that whatever is selected can be compared to the England lines. Is this possible?

1 ACCEPTED SOLUTION
Community Support

hi, @ABNHS84

Based on my research, you could use this way:

Step1:

Use this formula to add a dim group table.

`Dim Group = UNION(VALUES('Table'[Group]),ROW("Group","persistent"))`

Step2:

Create the relationship with basic data table.

Step3:

Create a measure by this formula:

```Measure =
IF (
SELECTEDVALUE ( 'Dim Group'[Group] ) = "persistent",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Group] = "England" )
),
CALCULATE ( SUM ( 'Table'[Value] ) )
)```

Step4:

Now in the time series line chart, drag Group field from Dim Group table.

and use Group filed from basic data table as a slicer.

Result:

However, whatever the slicers are set to (although England is not selected), the line for England to remain visible (persistend line).

and here is pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Community Champion

Hi @ABNHS84

The below should do the trick.

```england by measure =
CALCULATE(
[measure],
redion[country] = "England"
)```

Hope this helps.

Mariusz

Frequent Visitor

Thanks for the quick response Mariusz,

I don't think this solves by problem though.

I have data in the below format and want to produce a time series line chart. However, whatever the slicers are set to, I want to the line for England to remain visible.

```+---------+-------+-----+------+------+
| Group   | April | May | June | July |
+---------+-------+-----+------+------+
| England | 20%   | 25% | 30%  | 50%  |
+---------+-------+-----+------+------+
| Locale   | 10%   | 12% | 15%  | 30%  |
+---------+-------+-----+------+------+
| locale2  | 20%   | 40% | 60%  | 70%  |
+---------+-------+-----+------+------+```

Super User

Hi,

In the Query Editor, right click on the first column heading and Unpivot other columns.  Create a slicer for Group and ensure that England is always selected in that slicer (along with any other Group).  Now build your line chart visual.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

hi, @ABNHS84

Based on my research, you could use this way:

Step1:

Use this formula to add a dim group table.

`Dim Group = UNION(VALUES('Table'[Group]),ROW("Group","persistent"))`

Step2:

Create the relationship with basic data table.

Step3:

Create a measure by this formula:

```Measure =
IF (
SELECTEDVALUE ( 'Dim Group'[Group] ) = "persistent",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( ALLEXCEPT ( 'Table', 'Table'[Month] ), 'Table'[Group] = "England" )
),
CALCULATE ( SUM ( 'Table'[Value] ) )
)```

Step4:

Now in the time series line chart, drag Group field from Dim Group table.

and use Group filed from basic data table as a slicer.

Result:

However, whatever the slicers are set to (although England is not selected), the line for England to remain visible (persistend line).

and here is pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors