cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
semidevil Regular Visitor
Regular Visitor

powerbi line chart - have a line that is fixed.

I have a time series line chart that displays data over time for a particular state.  I also have a filter that will allow me to filter for cities, so I can see data over time for a particular city instead

 

what I want to add is another  line on my chart for the state, but have it in such a way that it is not affected by my filters.

 

Basically,  my result would be a line chart with 2 lines:  1 line that will show me data to a city(depending on my filter), and another line for the overall state line.  That way, I can compare "city" vs overall "state."

 

I thought adding a new column and aggregating the entire state data would do the trick, but it ended up being literally a flat line.

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: powerbi line chart - have a line that is fixed.

HI @semidevil

 

For your 'fixed' line, use a calculated measure and use the CALCULATE/ALL/ALLSELECTED functions to control the effect of external filters on the expression.  It's possible to write your calculation so it disregards external filters.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark Super Contributor
Super Contributor

Re: powerbi line chart - have a line that is fixed.

HI @semidevil

 

For your 'fixed' line, use a calculated measure and use the CALCULATE/ALL/ALLSELECTED functions to control the effect of external filters on the expression.  It's possible to write your calculation so it disregards external filters.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

semidevil Regular Visitor
Regular Visitor

Re: powerbi line chart - have a line that is fixed.

Thank you @Phil_Seamark.

  Here's the measure I attempted, and when I toggled my city slicer, the line still continue to move even though I expected it to stay at the 'state' level.

 

Measure= CALCULATE(SUM(Value),FILTER('Table1, Table1[Column1]="variable1"),FILTER(Table2,Table2[Column1]="variable2" || Table2[Column1]= "variable3"),ALL(City[City]))

 

 

I tried All, AllExcept, but it still did not seem to work.  I want all the rest of the filters to in and out of the query to still work....except the "city" slicer.

 

EDIT:  I just realized that when I removed the "Filter" inside my query, it seemed to solve the problem, and I just had to execute the filter in the GUI (visual level filter).  Is this by design?  I would like to have at least 1 filter in my query because my end goal is to have 3 line charts:  line 1 based on a filter, line 2 based on another filter from the same column, and line 3, the 'state' average. 

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 116 members 1,669 guests
Please welcome our newest community members: