cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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!

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!

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
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 56 members 947 guests
Please welcome our newest community members: