Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ABNHS84
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

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:

1.JPG2.JPG

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.

View solution in original post

4 REPLIES 4
Mariusz
Community Champion
Community Champion

Hi @ABNHS84 

The below should do the trick.

 

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

 

Hope this helps.

Mariusz

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%  |
+---------+-------+-----+------+------+

 

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
https://www.linkedin.com/in/excelenthusiasts/

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:

1.JPG2.JPG

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.