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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
semidevil
Helper I
Helper I

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
Phil_Seamark
Employee
Employee

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
Employee
Employee

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!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.